INDEX COLLECT, how to collect multiple values in a range when the criteria matches

Options
AdamT
AdamT ✭✭✭
edited 11/29/23 in Formulas and Functions

My question is similar to another user's question name of the same name, however I could not recreate what the responder advised. One column I have "Week" which is represented by a date. In another column I have "Unique ID". In a third column I have "Comments". In a pivot, I summarize each Unique ID into one row but in one column I want to pull all the "Comments" from each rows that has the same Unique ID. How do I do this?

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Hi @AdamT

    I believe a combination of JOIN and COLLECT will be your friend here. But I don't know what your plans for the "Week" column are.

    You can certainly COLLECT the values in your Comments column where the Unique ID matches the Unique ID in your "pivot table" column and JOIN these together with a delimiter. The CHAR(10) delimiter will create a new line between comments.

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    To illustrate how this works:

    If your data looks like this

    You can create a table like this


    Using this formula

    =JOIN(COLLECT(Comments:Comments, [Unique ID]:[Unique ID], [ID to look for]@row), CHAR(10))

    If you are doing this across two sheets you will need to replace the column references with cross sheet references.

    I hope this makes sense and is what you need. 🤞

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!