Join Collect
Hi i'm trying to find all of the timekeepers that worked for a specific client.
Normally I would use Index Match however, there are multiple time keepers that worked for the client and index match only shows one name.
For Index match I use the following formula: =INDEX({TimeKeeper}, MATCH([Matter Number]1, {Matter Number}, 0))
I tried Join collect but I keep getting a blank square could some assist.
=JOIN(COLLECT({TimeKeeper}, {Matter Number}, [Matter Number]1))
Thanks
Comments
-
Your formula looks correct. Have you double checked the references to make sure they didn't get messed up somehow? just click into the formula, then put the cursor on the reference, and select "Edit Reference". Make sure what you are referencing is highlighted, and that both ranges have the same amount of values.
-
Thank you,
I checked however it is still showing a blank square and everything matches.
-
If your Matter Numbers are actual numeric values, it will struggle to make a match. Try changing the Matter Numbers to text strings.
-
It already is text. Smartsheet only supports 5 decimals and he has 6 so he can't be showing it as a value. Didn't know this until I tried to recreate his problem
-
Just FYI, your formula should work. No reason it shouldn't. But I found a workaround that forces it to do what you want.
=JOIN(COLLECT({TimeKeeper}, {Matter Number}, @cell =[Matter Number]1))
For future reference if you have issues with a collect formula, try tossing some @cell references at your criteria. It really generally makes collect function more predictably.
-
thank you, this worked
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!