Vertical results with join collect

Hello!

I am working to pull in multiple employee names from a master file based on the facility they work at. I used the following formula. =JOIN(COLLECT({Job Categorization Range 2}, {Job Categorization Range 1}, [ADP employee ID (formula)]@row), " / ")

I am looking to populate the data into a smartsheet form list dropdown, but the I can only get a horizontal pull of data. I normally use transpose in excel but am less familiar in smartsheet. Can anyone assist?

Thank you!

Answers

  • Darren Mullen
    Darren Mullen ✭✭✭✭✭✭

    @Anna S I'm pulling in @Katy H . I think she has a trick using distinct index collect

  • Katy H
    Katy H ✭✭✭✭✭✭

    @Darren Mullen thank you for the tag!

    @Anna S as Darren mentioned I think this can be solved with INDEX(DISTINCT(COLLECT())) paired with INDEX(MATCH()) to pull in the remaining information.

    This requires a couple of helper columns depending on what information you are trying to pull in. I will use your example to pull in Employee ID's based on location from an employee master list and then add in a SUMIF formula to calculate total time worked by employee, pulled from an employee time sheet.

    For this exercise, here is my employee dataset that will serve as my reference sheet: https://app.smartsheet.com/b/publish?EQBCT=7225a34b9aaa4d79bec224d732363885

    This is the timesheet I am pulling the hours worked from: https://app.smartsheet.com/b/publish?EQBCT=748238e43c09402ca1f83685d9ef55f6

    And this is the sheet the sorts them by location and combines the data from the employee sheet and the timesheet: https://app.smartsheet.com/b/publish?EQBCT=22ba4dbb18804f88ac70fdad5929fa36

    INDEX(DISTINCT(COLLECT())) will pull in every unique instance of a value from a dataset, so is best used when looking at distinct values (employee ID being a good example of this). From there you can build INDEX/MATCH or other IF based formulas (SUMIFS, AVG(COLLECT()), etc) that use that ID value to search other datasets. This is a good way to either sort out large duplicative data sets, or to conjoin multiple data sets in a single location.

    Let me know if this works for your solution!

    Katy Hall

    Head of Product Management

    ILLA Canna

    LinkedIn

  • Darren Mullen
    Darren Mullen ✭✭✭✭✭✭

    @Katy H You're welcome. I wanted to see you work the magic. When I read that you liked using Index + Distinct + Collect, I make a sheet based on how I thought you did it. I more or less had it, except I didn't use the IFERROR and that makes a difference!

  • Anna S
    Anna S ✭✭

    This is helpful and I feel like I'm close. This is my formula and I am still getting an error message.

    =IFERROR(INDEX(DISTINCT(COLLECT({Job Categorization v2 Range 1}, {Job Categorization v2 Range 2, {Job Categorization v2 Range 3}, [ADP employee ID (formula)]@row, {Job Categorization v2 Range 4}, "PCT")), [Primary Column]@row, "N/A"))

  • Katy H
    Katy H ✭✭✭✭✭✭

    @Anna S I see a missing bracket, copy the exact formula after number 1 and see if you still get an error message. If the error message persists, try formula number 2 and then let me know the exact error you are seeing so I can help diagnose the issue.

    1. =IFERROR(INDEX(DISTINCT(COLLECT({Job Categorization v2 Range 1}, {Job Categorization v2 Range 2}, {Job Categorization v2 Range 3}, [ADP employee ID (formula)]@row, {Job Categorization v2 Range 4}, "PCT")), [Primary Column]@row, "N/A"))
    2. =INDEX(DISTINCT(COLLECT({Job Categorization v2 Range 1}, {Job Categorization v2 Range 2, {Job Categorization v2 Range 3}, [ADP employee ID (formula)]@row, {Job Categorization v2 Range 4}, "PCT")), [Primary Column]@row)


    Katy Hall

    Head of Product Management

    ILLA Canna

    LinkedIn

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!