Using INDEX/COLLECT to return multiple values

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

Answers

  • Estela Galicia
    Estela Galicia ✭✭✭
    edited 06/18/24
    Options

    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
    AravindGP ✭✭✭✭✭✭
    Options

    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

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!