Vertical results with join collect

Anna S

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!

Darren Mullen

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

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!

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

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

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