Convert Multi-Select to Single Rows in Excel or Sheet

Bethany Garcia

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

Genevieve P.

Hi @Bethany Garcia

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

Genevieve P.

Hi @Bethany Garcia

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

Bethany Garcia

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

Kate Kelley

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

Genevieve P.

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?

Kate Kelley

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

Genevieve P.

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

Kate Kelley

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

Genevieve P.

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

Kate Kelley

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?

Genevieve P.

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?

Kate Kelley

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

CAS the CSA

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

heyjay

=SUBSTITUTE(
[Column]@row, 
CHAR(10), ", ")