Join Collect

Karrie Davis

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.

Paul Newcome

Try something like this...

 

=JOIN(COLLECT({Timekeeper}, {Matter Number}, [Matter Number]@row), "delimiter")

Dominique Rose Winther

Ooooo, I was stumped on using Join Collect until I found your post. Thank YOU!

David Joyeuse

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")