Parsing a multi select column into separate rows

Paul Reeves

I am wondering if there is a way to parse a multi-select dropdown column into different rows ideally on the Intake sheet, or a separate sheet. In my use case the requestor would submit a form with a multi-select field, e.g. Doc Type - ZIM, ZOR, QT. I am thinking the Intake file would either be split into 3 rows, one for each Doc Type. Or, there is some sort of extract that creates the three separate row on a supporting worksheet.

With Excel I would have to write a macro.

Kelly P.

@Paul Reeves

If you want the process to be completely automatic and not use API, you could try something like this:

1) In your intake sheet, add a new column called "Document Type" (or some variation of Doc Type). Leave it blank.

2) Save as new your intake sheet (without data or automations) four times: once for each of the three doc types (these will be intermediate doc-type sheets) and once for a final consolidated sheet (where the rows come back together in a single sheet).

3) In each of the three intermediate doc-type sheets, add an automation that populates "Document Type" with the value matching that sheet (for example, the automation in your ZIM intermediate sheet would place "Zim" in the Document Type field when a new row arrives). Add a second automation that moves the row to your consolidated sheet once the Document Type has a value.

4) In your intake sheet, add an automation with three condition paths. The shared trigger can be "when rows are added when the primary column changes to any value." The three condition paths would be a) where Doc Type has any of Zim; b) where Doc Type has any of Zor; c) where doc type has any of QT. The actions would be to copy the row to the corresponding intermediate sheet.

The flow would then be: a form is submitted into the intake sheet; it has one or more values in the Doc Type field and nothing in the Document Type field. The automation copies the row to the doc-type intermediate sheet(s) according to the doc type field entries. The original row remains in the intake sheet. Once arriving in the intermediate sheet(s), the Document Type field is populated with a single value. The row is then moved to the consolidated sheet, leaving the intermediate sheet empty. Thus, your existing intake sheet will retain a history of form submissions, your intermediate sheets have rows briefly but are mostly empty, and your consolidated sheet will have 1, 2, or 3 rows from each form submission.

Hope this helps!