Join Collect
Hi I'm trying to to create a sheet that tells me all of the timekeepers that work on a client from data from another sheet.
Currently I have sheet that list all of our clients and I will like to add a column that list all of the timekeepers that work on the project. Normally I would use Index Match however, some clients have multiple time and index match only show one timekeeper is there a formula to show multiple timekeepers.
Here is the index/match formula I use: =INDEX({TimeKeeper}, MATCH([Matter Number]1, {Matter Number}, 0)
In the below example you can see the index match that only shows one name, however for client Red there should be 4 names. Any help would be appreciated.
Comments
-
Try something like this...
=JOIN(COLLECT({Timekeeper}, {Matter Number}, [Matter Number]@row), "delimiter")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Ooooo, I was stumped on using Join Collect until I found your post. Thank YOU!
-
You can also use DISTINCT with it.
Collect grabs all the Data that suits, Distinct will keep only one occurence of each so you don't have Big Bird displayed 3 or 4 times.
That would be this then:
=JOIN(DISTINCT(COLLECT({Timekeeper}, {Matter Number}, [Matter Number]@row)), "delimiter")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!