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

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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 430 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 75 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!