Returning results for identifier as a single identifier or within a join from the same column

Hello! I have a complicated formula that I need help writing. I have one sheet with three columns, Column A is the 'Created' column, Column B is the score given via form, Column C is either a single identifier or joined identifiers based on the form selection. I want to pull the latest score into another sheet which lists each identifier as a single record. So the formula needs to recognize the Max date as well as the identifier that may be contained within a join or a single identifier. Any gurus out there that want to take this one.

Answers

  • Andrea_F
    Andrea_F ✭✭
    edited 08/24/23

    I ended up adding a help column of joined identifiers and basically saying IFERROR (NOMATCH) on the single identifier, then use the new joined column, and IFERROR still " ". Here is the formula:

    =IFERROR(INDEX(COLLECT({Column B}, {Column C}, [Single Identifier]@row, {Column A}, MAX(COLLECT({Column A}, {Column C}, [Single Identifier]@row))), 1), IFERROR(INDEX(COLLECT({Column B}, {Column C}, [Joined Identifier]@row, {Column A}, MAX(COLLECT({Column A},{Column C}, [Joined Identifier]@row))), 1), ""))

  • Hi @Andrea_F

    Thanks for posting your solution! 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!