Normalize Data in Google Sheets - TRANSPOSE multiple columns Fill Down

Question

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

Can you give view or edit access? – Eliana Cohen Mar 26 '19 at 13:19 — Eliana Cohen, Mar 26 '19 at 13:19

player0 · Accepted Answer · 2019-04-01T15:47:15.127

3

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<>''"))

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

edited Apr 01 '19 at 15:47

answered Mar 29 '19 at 12:02

player0

124,011
12
67
124

Thanks. It breaks when I added more data. It looks like this is best formula (KPV Test Tab): =ArrayFormula(Query(TRIM(split(transpose(split(textjoin("|",1,if(len(C3:C),REGEXREPLACE(C3:C&",",",","|"&A3:A&"|"&B3:B&","),)),",")),"|")),"Select Col2,Col3,Col1")) – moonshot Apr 01 '19 at 14:40
well, you kinda broke the pattern with adding different stuff (non-comma list and items with space). answer updated and formula fixed – player0 Apr 01 '19 at 15:45
1

this is great, thanks. sorry about that. I should have added more variations to begin with. – moonshot Apr 01 '19 at 21:35
2

This is exactly what I needed. Thank you! In case anyone else was confused by the "♥" & "♦" - they're meant to be characters which are very unlikely to be found in a normal spreadsheet. @player0 suggestion works well, but in my work it would error out after 140 rows (50 rows in the spreadsheet above). moonshot's KPV (KPV?) formula works with more rows, and also has bonus removal of whitespace after comma with REGEXREPLACE and TRIM. Good work. – togume Jul 03 '20 at 13:15

ttarchala · Answer 2 · 2019-03-26T18:27:18.183

This rather long formula will do what you want

=query(
  arrayformula(
    split(
      transpose(
        split(
          textjoin(
            "\",
            0,
            $B3:B4 &
              "|" & 
              split(C3:C4, ", ")
          ),
          "\"
        )
      ),
      "|"
    )
  ),
  "select * where Col2 <> ''"
)

Explanation: really too long and too boring to write. Just rebuild the query starting from the innermost formulas to see how it works.

Adding the column A is left as an exercise to the reader.

Normalize Data in Google Sheets - TRANSPOSE multiple columns Fill Down

2 Answers2