Convert Multi-Select to Single Rows in Excel or Sheet
Hello,
I have a need to use a Form to capture training completion, where the supervisor can select multiple values from an employee dropdown to select everyone who completed the training course. The attached image shows what that would look like within the sheet, where the employee name is multiselect.
We then need to output the information with a single row per employee, seen in the attached screenshot. Does anyone know how we can achieve this either by using Excel/Notepad with an export of the sheet, or to use formulas or other capabilities to do this within Smartsheet itself?
Thank you,
Bethany
Best Answer
-
Do you have a set list for the Employees?
If so, what I would do is have a second sheet with all of the Employee names pre-populated down one column. Then you can use a JOIN(COLLECT formula for both the "Training" column and the "Supervisor" column to pull that data from your Form collection sheet, like so:
=JOIN(COLLECT({Training Column Form Sheet}, {Employee Column Form Sheet}, HAS(@cell, [Employee List]@row)), CHAR(10))
That way if there are multiple trainings submitted for a single Employee it will gather all of the Training titles together in one cell for you, too. Note that I have CHAR(10) at the end to separate the values, so you'll want to place the formula in a multi-select column.
See: Formula combinations for cross sheet references
You can do the same for the Supervisor column:
=JOIN(COLLECT({Training Column Form Sheet}, {Employee Column Form Sheet}, HAS(@cell, [Employee List]@row)), CHAR(10))
Let me know if this makes sense and works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Do you have a set list for the Employees?
If so, what I would do is have a second sheet with all of the Employee names pre-populated down one column. Then you can use a JOIN(COLLECT formula for both the "Training" column and the "Supervisor" column to pull that data from your Form collection sheet, like so:
=JOIN(COLLECT({Training Column Form Sheet}, {Employee Column Form Sheet}, HAS(@cell, [Employee List]@row)), CHAR(10))
That way if there are multiple trainings submitted for a single Employee it will gather all of the Training titles together in one cell for you, too. Note that I have CHAR(10) at the end to separate the values, so you'll want to place the formula in a multi-select column.
See: Formula combinations for cross sheet references
You can do the same for the Supervisor column:
=JOIN(COLLECT({Training Column Form Sheet}, {Employee Column Form Sheet}, HAS(@cell, [Employee List]@row)), CHAR(10))
Let me know if this makes sense and works for you!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. that is actually what I'm planning to do, I thought more about it and this approach popped in my head a few days ago. Thank you for validating my direction, and providing some sample formulas I can use for this!
-
@Genevieve P. - I'm hoping you can help. This seems to be the closest and cleanest solution to something I'm considering. We are trying to have someone complete a form and select users to award points. Basically an attendance sheet where the instructor is completing a form. They will select the session, then using logic, that session will have the attendees prepopulated so they can multi-select who to award points to. My ideal end goal would then be to have a separate sheet (using above approach), list the session and learners in separate rows. The issue I'm thinking I will run into using the above solution is that if someone receives points for multiple sessions attended, can we create separate rows for them to capture the multiples?
-
Hi @Kate Kelley
There currently isn't a way to parse out a multi-select cell into multiple, individual rows. The solution above actually does the opposite: it takes individual rows and Joins them together into one multi-select cell.
In your scenario, the second sheet would have the Users as unique rows already populated, then you could use the JOIN formula to show the Sessions associated with each in a multi-select cell. Does that make sense?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. - Possibly? I think it's close! We have a roster of everyone at the conference, so we can have the second sheet prepopulated with the users in unique rows. So col. A would be Users, and col. B would be the sessions they attended. I'm just not sure on the Join formula. Below is what I attempted but returned Unparseable. It would be great that if there were multiple submissions, that they would populate in different columnts on the second sheet with the user prepopulated.
=JOIN(COLLECT({New Sheet Range 1}, HAS([Smartsheet Name]@row (Smartsheet Name@row))))
-
Hi @Kate Kelley
Here's how you would set up the formula in the sheet that has Col. A with the Users, putting it in Col. B:
=JOIN(COLLECT({Sessions Attended Column}, {User Names Column}, HAS(@cell, [Smartsheet Name]@row)), ", ")
This would collect the Sessions into one cell, next to the User. Let me know if it works!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. - The formula works to the extent (there was an extra ) after @row) that it says calculating and doesn't return Unparseable. There's something wrong with my sheet references.
For {Sessions Attended Column} - I referenced just the sessions attended column on original sheet
For {User Names Column} - I referenced just the names column on the original sheet.
Using those references, they returned blank cells. Should I be including more columns in the references?
Demo sheets: https://app.smartsheet.com/folders/qM8x5wMhJR7MqH4xf3c8PJgVfmX95qJPw6V8h9r1
Thank you for all your help with this!! I know we're so close!
-
Hi @Kate Kelley
Is there any possibility that the formula is not finding a match for the names?
For example, instead of the name in the [Smartsheet Name] column saying "Genevieve P" it says "Genevieve" so it's not finding a match?
Try using a COUNTIFS just to make sure our criteria are correct:
=COUNTIFS({User Names Column}, HAS(@cell, [Smartsheet Name]@row))
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Yes, they're all copy/paste. I'm setting it up as a test case so I've input all the data. I created a list of names and copy/pasted for the multi-select drop down and also on second sheet where I'm using the JOIN formula.
I did get number returns using the COUNTIFS, so that's why I'm thinking my JOIN references are off. For the references, should I just be selecting the single columns on the original sheet?
-
Thanks for confirming that the COUNTIFS works, @Kate Kelley!
For the references, yes, you should be clicking the individual column names to highlight that one column:
- {Sessions Attended Column} - the single column that lists all the Sessions
- {User Names Column} - the multi-select column that has the users associated
If this is still returning 0, can you show both source and destination sheets in a screen capture (blocking out sensitive data) and show the current formula open?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. Actually, I got it!!! I deleted all the sheet references to start from scratch, and that worked!!!! Thank you so much for this!
-
I have a similar issue where the multiple selections can't be used in another cell as a string of text. It forces a line break we don't want.
I need LICS 3b to display " MA RI VT " with any delimeter, but not in a list as shown above.
I've tried text( & join(collect(
not sure how to get rid of those line breaks
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!