Search code examples
loopsgoogle-sheetssplitflattengoogle-query-language

Repeat Rows per number of elements in next column

I have the following table:

table 1

However, I need to convert it into something like this:

table 2

Meaning that I need to repeat the first Cell times the number of elements in the next cell (assuming we will split them by ";"). I've tried several approaches, but so far nothing came close to the final table I am showing in the picture. Any help will be greatly appreciated.

Solution

  • use:

    =INDEX(QUERY(SPLIT(FLATTEN(A2:A&"×"&SPLIT(B2:B, ";")), "×"), 
 "where Col2 is not null", ))