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

to:

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?

CodePudding user response：

try:

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

CodePudding user response：

You can use the custom "UNPIVOT" function