Create new array/rows using CSV in single cell in Google Sheets

Viewed 25

Using the cell with CSV as the reference, I would like to create a new array/row set with each variation.

| Original                  | Desired       
| Name  | Parameters        | Name  | Parameters
---------------------------------------------------
| Co1   | Param1            | Co1   | Param1
| Co2   | Param2, Param3    | Co2   | Param2
| Co3   | Param3            | Co2   | Param3
| Co4   | Param2,  Param1   | Co3   | Param3
|       |                   | Co4   | Param2
|       |                   | Co4   | Param1

Some progress has been made. https://docs.google.com/spreadsheets/d/1rbr_xlDCtP9fkzx_zoTJ1Qi3ZU2Jg9bPfmba_WRb9T0/edit?usp=sharing

"Achieved so far" uses this formula:

ArrayFormula(IF(A3:A <> "",{A3:A,QUERY(SPLIT(B3:B, ", "), "SELECT *")},""))

"And this" uses this formula:

=FLATTEN(F3:G6)

I feel like I'm just one more step away

1 Answers

use:

=INDEX(QUERY(SPLIT(FLATTEN(A3:A&"×"&TRIM(SPLIT(B3:B, ","))), "×"), 
 "where Col2 is not null", ))

enter image description here

Related