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, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
@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 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!
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
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"))
-
@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.
- =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"))
- =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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!