Using INDEX/COLLECT to return multiple values

System

This discussion was created from comments split from: Using INDEX/COLLECT to return multiple values.

Estela Galicia

Hi @Genevieve P.

Im looking to find a way to list all projects per Resource/Stáff. The sheet that we have currently lists staff allocation from different columns as there needs to be a few staff working per project.

Is there a way for join collect to bring matches from different columns and return in 1 cell?

Second, how can I add criteria for Join Collect that it only brings back the projects for 2025 (we have a column for dates) and not previous years?

The project and staff allocation file looks something like the one below.

	Resource 1	Resource 2	Resource 3	Resource 4
Project 1	Marie	Gill
Project 2		Sarah	Roda	Grace
Project 3	Sarah	Roda		Veronica
Project 4	Marie	Veronica	Sarah
Project 5	Gill	Roda	Grace
Project 6	Marie	Gill
Project 7		Sarah	Roda	Grace
Project 8	Sarah	Roda		Veronica
Project 9	Marie	Veronica	Sarah
Project 10	Gill	Roda	Grace

I appreciate your help.

Thanks

Estela

AravindGP

Hi Estela,

It is not a very clean solution, but you can try this. Assuming that you've a column named Resource in the same sheet or a different sheet (the brackets will change) and named the first column as Projects, you can use this formula to get all the projects per resource.

=JOIN(COLLECT(Projects:Projects, [Resource 1]:[Resource 1], [Resource]@row), ", ") + ", " + JOIN(COLLECT(Projects:Projects, [Resource 2]:[Resource 2], [Resource]@row), ", ") + ", " + JOIN(COLLECT(Projects:Projects, [Resource 3]:[Resource 3], [Resource]@row), ", ") + ", " + JOIN(COLLECT(Projects:Projects, [Resource 4]:[Resource 4], [Resource]@row), ", ")

jmyzk_cloudsmart_jp

Hi @Estela Galicia

As @AravindGP commented, we need a column named Resource on the same sheet or a different one.

Using this formula, I get a list of distinct resources in the demo solution below. (Note. I use a Helper column [RowNum])

=IFERROR(INDEX(DISTINCT({Resource 1:4}), RowNum@row), "")

Here, the range {Resource 1:4} refers to the [Resource 1]:[Resource4} range in the source sheet. (See the next image.)

I added a helper column, [JOIN(DISTINCT(Resources))] at the source sheet to JOIN(COLLECT()) "List of All Projects per Resource".

[JOIN(DISTINCT(Resources))] = JOIN(DISTINCT([Resource 1]@row:[Resource 4]@row), ", ")

The formula returns a list of staff in a given project in text format, such as "Marie, Gill" for Project 1.

Using this value, I get "List of All Projects per Resource" with the following formula;

[List of All Projects per Resource] = JOIN(COLLECT({Project}, {JOIN(DISTINCT(Resources))}, CONTAINS(Resource@row, @cell)), ", ")

As for the Join Collect that only brings back the projects for 2025, the formula is like this;

[List of All Projects per Resource 2025] =JOIN(COLLECT({Project}, {JOIN(DISTINCT(Resources))}, CONTAINS(Resource@row, @cell), {Year}, 2025), ", ") 

https://app.smartsheet.com/b/publish?EQBCT=012f70933dd24a30918f71c7dcdfe964

The Source Sheet

https://app.smartsheet.com/b/publish?EQBCT=b07f3161931041a7a445569ec25e8941

Alternative Solution without helper column in the source sheet

If you want to avoid adding a helper column, [JOIN(DISTINCT(Resources))] at the source sheet, here is an alternative solution. (I found the first solution easier.)

https://app.smartsheet.com/b/publish?EQBCT=4fa18859c0a74e7794435eb6c90ebfce