Split Multiple Selections In Single Form Into Individual Rows (Core Product)
There have been a number of people asking how to make multiple selections in a single form and then have those selections broken down into individual rows, and the previous answer was always to use a 3rd party app, a premium add-on, or the API.
Below is a set of detailed instructions on how to accomplish this within the core product using two sheets and a copy row automation. The below is set up with new form entries being populated at the top of the sheet for ease of formulas, but it can be adjusted so that new form entries are made at the bottom of the sheet. I wasn't too worried about the look of the initial submission sheet though as the selections get parsed out and put on individual rows on a second sheet.
Here is how we take form submissions that look like this:
And turn them into static entries that look like this:
STEP 1 - Set Up the Base Form Sheet
The first thing to do is set up the Form Sheet. This sheet is the one that has the form attached to it and is also used to shift each form submission from horizontal to vertical. This example is a basic setup where we capture a name and 5 separate options. You are also going to need an auto-number column with no special formatting. This solution can be adapted to use special formatting in the auto-number column, but I am keeping things simple here to get the basic ideas across. In this example, I called the auto-number column [Submission #]. The other columns below are all text/number type columns, and these are the ones we will find on the form.
STEP 2 - Set Up the Formatting Section of the Form Sheet
Next we need to set up our formatting section.
There is a checkbox column called [Copy Section] that is manually checked on 5 rows. If we had more or less options available that needed to be split, we would use that same number of rows manually checked.
We need a text/number column called [Option]. This has a formula in each of the 5 rows. This is what converts our horizontal format into a vertical format. We use an INDEX function to pull the top row from each of the individual option columns.
=INDEX([Option 1]:[Option 1], 1)
=INDEX([Option 2]:[Option 2], 1)
=INDEX([Option 3]:[Option 3], 1)
=INDEX([Option 4]:[Option 4], 1)
=INDEX([Option 5]:[Option 5], 1)
Then I used the Primary Column and called it [Name]. You can use any text/number column, but I used it this way so that the name is in the Primary Column of the Final Sheet. I used this formula in all 5 of the rows:
=IF(Option@row <> "", INDEX([Form Name]:[Form Name], 1))
Next is a text/number column called [Submission Number]. I used this formula on all 5 rows:
=IF(Option@row <> "", MAX([Submission #]:[Submission #]))
Now we should have a section in the Form Sheet that looks like this:
And the whole Form Sheet (after a few test forms) looks like this:
STEP 3 - Set Up the Final Sheet
Now we can create our Final Sheet. To do this, I just created a new sheet, renamed the Primary Column to [Name], then copied a row from the Form Sheet to the Final Sheet to get the rest of the columns pushed through. I also deleted the standard columns that are automatically put in when creating a new sheet
STEP 4 - Copy Row Automation in Form Sheet
Then we hop back over to our Form Sheet and set up a Copy Row automation that looks like this:
Make a few test submissions to make sure everything is in fact linking up, and we should be up and running.
NOTE: We can also modify this to pull from submissions made using a multi-select dropdown, but this example is kept simple to provide the basics on getting this set up.
