Split Multiple Selections In Single Form Into Individual Rows (Core Product)

chris.wang

That would be the

=IF(Option@row <> "", MAX([Submission #]:[Submission #]))

formula you had provided. (Total newbie to Smartsheets) I am trying to align column headers as much as I can with your example! @Paul Newcome

chris.wang

https://community.smartsheet.com/discussion/comment/447347#Comment_447347

Hey @Paul Newcome, sorry about the delay - my reply did not go through. I had copied your formula -

[Submission Number] =IF(Option@row <> "", MAX([Submission #]:[Submission #]))

[Option] =IFERROR(MID("!" + SUBSTITUTE(INDEX([Category - Industry of work?]@row, 1), CHAR(10), "!") + "!", FIND("~", SUBSTITUTE("!" + SUBSTITUTE(INDEX([Category - Industry of work?]@row, 1), CHAR(10), "!") + "!", "!", "~", Number@row)) + 1, FIND("~", SUBSTITUTE("!" + SUBSTITUTE(INDEX([Category - Industry of work?]@row, 1), CHAR(10), "!") + "!", "!", "~", Number@row + 1)) - (FIND("~", SUBSTITUTE("!" + SUBSTITUTE(INDEX([Category - Industry of work?]@row, 1), CHAR(10), "!") + "!", "!", "~", Number@row)) + 1)), "")

[Name] =IF(Option@row <> "", INDEX(Key@row, 1))

This is how it's looking now. (This is only on the first sheet where Forms come in, I have not connected it to the second sheet yet)

Paul Newcome

@chris.wang What is in the [Submission #] column? This should be an auto-number type column.

chris.wang

Hey @Paul Newcome,

I was reviewing through the other comments and noticed that most of the examples have multiselects options of only a few options such as A-E, 1-5. Is this still an applicable solution for a form that has a question that has over 400 multiresponse options?

Thanks -

chris.wang

Hey @Paul Newcome just wanted to follow up on my last comment!

Paul Newcome

@chris.wang I haven't had any issues with scaling thus far, but I also have not had to scale that far. The best I can tell you is to give it a try and see how it works.

Kate H

Hey @Paul Newcome this process is awesome and exactly what I needed! I am having trouble utilizing this for more than one row. The way I have this setup, it works for Multi Select Test 3 but doesn't register Test 2 and Test 1. Any idea why it doesn't register these? Do you have to hand update the formula in the [Option] and [Name] columns to create rows for each possibility? Like should I have the first 5 reference …Test 3 and the next 5 change to reference …Test 2?

This is Form Sheet: I really am just focused on getting the Form Name and Multi Select Columns to create the new rows on Final Sheet, one for each multi selected option.

Final Sheet seems to look fine. It copies over what it should.

Paul Newcome

@Kate H This particular setup will only show the newest row on that sheet. It is designed to copy the bottom section over to the target sheet as new rows are added through a form (meaning new rows added one at a time). The bottom section is not designed to show all row all at the same time.

Kate H

@Paul Newcome thanks for the response. Is there anyway to add or change it to make it see all?

Paul Newcome

That would be the second sheet in this solution. The sheet that is the recipient of the copy row automation from this sheet.

Kate H

@Paul Newcome yes I can make the final sheet work fine but I'm not creating inputs to these formulas in the same way - with a form one at a time. I'd like to have the person copy paste from 30-150 lines into a sheet, select the multi select options to denote the groups impacted, then this process create a row for each multiselect for all 30-150 rows added in the form sheet without having to use a form. If it's a predefined area, say just create 150 cells in a specific column, can you help me make a loop that instead of looking at the newest one, that it looks at all 150 and processes them?

Paul Newcome

You would have to recreate the Copy Section that many times going down the sheet. You would then adjust the formula that pulls in the MAX to use a LARGE function instead. The first Copy Section would be 1. The second Copy Section would be 2, so on and so forth.

NeilKY

https://community.smartsheet.com/discussion/comment/455099#Comment_455099

@Kate H , did you get this up and working? If so, i would LOOOVE to have a copy of your template. I'm trying to do the same thing but man it's hard to follow these posts for me as a newbie.

NeilKY

@Paul Newcome I'm having an issue with copying only the data wanted to the new sheet. I'm not quite sure why. Here are screenshots of the data, using multi-select. Maybe you can tell from this?

Before submitting the form:

Formulas on this sheet:

Number: (plain text, manually input numbers)

Option: (Rows 4-13 are the same) =IFERROR(MID("!" + SUBSTITUTE(INDEX([Multi-Select]:[Multi-Select], 1), CHAR(10), "!") + "!", FIND("~", SUBSTITUTE("!" + SUBSTITUTE(INDEX([Multi-Select]:[Multi-Select], 1), CHAR(10), "!") + "!", "!", "~", Number@row)) + 1, FIND("~", SUBSTITUTE("!" + SUBSTITUTE(INDEX([Multi-Select]:[Multi-Select], 1), CHAR(10), "!") + "!", "!", "~", Number@row + 1)) - (FIND("~", SUBSTITUTE("!" + SUBSTITUTE(INDEX([Multi-Select]:[Multi-Select], 1), CHAR(10), "!") + "!", "!", "~", Number@row)) + 1)), "")

Name: (Plain text, "Multi-Select Test 3" for all rows with text)

Submission Number: Column Formula of: =IF(Option@row <> "", MAX([Submission #]:[Submission #]))

Submission #: Autonumber Column Format

Form Name: Plain Text

Multi-Select: Multi-Select format

Here's the workflow:

Here is the form and what I put in the fields:

After Submitting, it adds the row to the form sheet as it should:

But on the Final Sheet, this is the output:

I expected it to only output the LAST row into 5 rows, one for each item in the multi-select field. Just like you had in your original post.

Below is how i expected it to look:

Where's the disconnect? Can someone please tell me:

1. Which rows do what?
2. Which fields are output?
3. Which fields and values matter in the form?

My specific use is for ordering supplies. so the products would be things like "Pen, pencil, paper, staples" etc. instead of the Options A-J.

NeilKY

PS. Thanks in advance!