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

Options

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
    Options

    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), ""))

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Andrea_F

    Thanks for posting your solution! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!