Join Collect

Karrie Davis · 11/19/19

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

Data sheet_0.PNG

data 2_0.PNG

L_123 · 11/19/19

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.

Karrie Davis · 11/19/19

Thank you,

I checked however it is still showing a blank square and everything matches.

Paul Newcome · 11/22/19

If your Matter Numbers are actual numeric values, it will struggle to make a match. Try changing the Matter Numbers to text strings.

L_123 · 11/22/19

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

L_123 · 11/22/19

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@cellreferences at your criteria. It really generally makes collect function more predictably.

Karrie Davis · 11/25/19

thank you, this worked

Join Collect

Comments

Help Article Resources

Categories