Using INDEX/COLLECT to return multiple values
Answers
-
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
-
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), ", ")
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
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), ", ")
The Source Sheet
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.)
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
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!