Explode each row into multiple rows by splitting a column of a given computed range

Question

I was recently tasked with 'exploding' each row in a given range with respect to the split value of one of the columns, i.e. going from

Name	Interests	Age
John	swimming, movies	31
Mary	basketball	26
Richard	football, music	21

to:

Name	Interest	Age
John	swimming	31
John	movies	31
Mary	basketball	26
Richard	football	21
Richard	music	21

It's a little similar to a Cartesian product, only one of the terms needs to be computed on the basis of the value in the Interests column. I eventually solved it using an Apps Script function, but I'm wondering if it could be easily solved using a regular formula.

Note that the input range in my case was a product of another formula (a QUERY(...), to be exact), so not necessarily contiguous or addressable within the spreadsheet.

Any ideas?

Answer 1

try:

=INDEX(QUERY(SPLIT(FLATTEN(A1:A&"×"&SPLIT(B1:B, ", ", )&"×"&C1:C), "×"),
 "where Col3 is not null"))

Answer 2

You can use the custom "UNPIVOT" function found on this sheet. File>Make a Copy to grab the script. Also here on github.

=ARRAYFORMULA(UNPIVOT(A2:A,"V",SPLIT(B2:B,", ",0),"B",C2:C,"V"))

You would then QUERY() the output to eliminate the rows where there was nothing in the second column.