I have rows of data with columns like example below: Current Format
I want to pull the data in another sheet as follows:Desire Format
I tried a combination of query,split and transpose but am not getting the result I want. Is this possible at all?
Link of spreadsheet: https://docs.google.com/spreadsheets/u/0/d/1ct7HLdvgkY0tYaiUyduhehyci7Dt7p_ajNCKcJandS0/htmlview
I added two sheets ("Sheet1B" and "Erik Help") to your sample spreadsheet.
'Sheet1B' is just a cleaned-up version of your 'Sheet1'. This sheet reflects the real-world layout. As such, my formula in 'Erik Help' references 'Sheet1B' and not 'Sheet1."
There is one formula in 'Erik Help'!A1:
=ArrayFormula({Sheet1B!A1:J1; QUERY(SPLIT(FLATTEN(FILTER(SUBSTITUTE(SPLIT(SUBSTITUTE(Sheet1B!A2:A & "|" & Sheet1B!B2:B & "|" & Sheet1B!C2:C & "|" & Sheet1B!D2:D,",","~") & REPT(",|||",LEN(Sheet1B!E2:E)-LEN(SUBSTITUTE(Sheet1B!E2:E,",",""))),",",1,0),"~",",") & "|" & SPLIT(Sheet1B!E2:E,",") & "|" & SPLIT(Sheet1B!F2:F,",") & "|" & SPLIT(Sheet1B!G2:G,",") & "|" & SPLIT(Sheet1B!H2:H,",") & "|" & SPLIT(Sheet1B!I2:I,",") & "|" & SPLIT(REPT(",",LEN(Sheet1B!E2:E)-LEN(SUBSTITUTE(Sheet1B!E2:E,",",""))) & Sheet1B!J2:J,",",1,0),Sheet1B!A2:A<>"")),"|",1,0),"Select * WHERE Col5 Is Not Null")})
This one formula produces all headers and results seen and will expand to include new rows of data.
As you can see, I strongly recommend placing the formula in its own sheet, rather than in the same sheet as the raw data. However, if you must place it in the same sheet as the raw data, do not place it below the raw data, since this would inhibit the ability of the raw data range to grow naturally. Place it to the right. In that case, given the current layout of your 'Sheet1' (and assuming you delete all data from Row 7 down, the formula would look like this:
=ArrayFormula({A3:J3; QUERY(SPLIT(FLATTEN(FILTER(SUBSTITUTE(SPLIT(SUBSTITUTE(A4:A & "|" & B4:B & "|" & C4:C & "|" & D4:D,",","~") & REPT(",|||",LEN(E4:E)-LEN(SUBSTITUTE(E4:E,",",""))),",",1,0),"~",",") & "|" & SPLIT(E4:E,",") & "|" & SPLIT(F4:F,",") & "|" & SPLIT(G4:G,",") & "|" & SPLIT(H4:H,",") & "|" & SPLIT(I4:I,",") & "|" & SPLIT(REPT(",",LEN(E4:E)-LEN(SUBSTITUTE(E4:E,",",""))) & J4:J,",",1,0),A4:A<>"")),"|",1,0),"Select * WHERE Col5 Is Not Null")})
