I am trying to write a formula that will take a set of columns and pair them into separate rows with a comma-delimited list. In SQL, I would do this with a left join, but I am not sure how to leverage GSheets functions for this.
Here is what I mean:
Source Data
| Col1 | Col2 | CommaDelim |
|---|---|---|
| Col1Val1 | Col2Val1 | 1,2,3 |
| Col1Val2 | Col2Val2 | 1 |
| Col1Val3 | Col2Val3 | 1,2 |
| Col1Val4 | Col2Val4 | 1,2,3,4 |
Desired Output
| Col1 | Col2 | CommaDelim |
|---|---|---|
| Col1Val1 | Col2Val1 | 1 |
| Col1Val1 | Col2Val1 | 2 |
| Col1Val1 | Col2Val1 | 3 |
| Col1Val2 | Col2Val2 | 1 |
| Col1Val3 | Col2Val3 | 1 |
| Col1Val3 | Col2Val3 | 2 |
| Col1Val4 | Col2Val4 | 1 |
| Col1Val4 | Col2Val4 | 2 |
| Col1Val4 | Col2Val4 | 3 |
| Col1Val4 | Col2Val4 | 4 |
CodePudding user response：
try:
=INDEX(QUERY(SPLIT(FLATTEN(A1:A&"×"&B1:B&"×"&SPLIT(C1:C, ",")), "×"),
"where Col3 is not null"))
