google-sheetsgoogle-sheets-formulatransposearray-formulasgoogle-sheets-query

Normalize Data in Google Sheets - TRANSPOSE multiple columns Fill Down


I have data I need normalized. Any value in Col C separated by a comma needs its own row.

Here is my sheet

The Data is in Col A - Col C and the Desired Result is int Col E-ColG.

This question was helpful and I got the formula working for col C but when I try to integrate col A and B it breaks:

Transpose, Split, and Join Google Sheets


Solution

  • a scalable modification of @ttarchala's solution:

    =ARRAYFORMULA(QUERY(SPLIT(TRANSPOSE(SPLIT(IFERROR(TEXTJOIN(
     "♦", 1, $A3:A&"♠"&$B3:B&"♠"&IFERROR(SPLIT(C3:C, ", ")))), "♦")), "♠"),
     "where Col2<>'' and Col3<>''"))
    

    0


    =ARRAYFORMULA(QUERY(SPLIT(TRANSPOSE(SPLIT(IFERROR(TEXTJOIN( "♦", 1, 
     SUBSTITUTE($A3:A&"♠"&$B3:B&"♠"&IFERROR(SPLIT(
     REGEXREPLACE(C3:C, ", |,", "♥"), "♥")), "♠♠", ""))), "♦")), "♠"), "where Col3<>''"))
    

    0