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

AdamT

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?

KPH

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

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. 🤞