Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygoogle-query-language

how to reshape input dataset with formula

I want to work the following. I am using this formula

=ARRAYFORMULA(Split(Transpose(Split(Query(Transpose(query(transpose(if(Input!B2:I<>"", ";"&Input!A2:A&"\"&Input!B2:I, )) ,,999^99)),,999^99), ";")), "\"))

but it does not give the desired results. Here is the desired output 'Automatically restructure all data from input tab as "Example Output" tab illustrates
No rows for "blank" cells in the input tab
Use formula(s) only in the first row - i.e. no need to drag cells down the entire sheet, and this tab auto-updates when new entry made in Input tab" get the sheets on this link and give ideas on how to improve the formula or insights into how you can do it differently

Solution 
  • =QUERY({Input!A2:C; 
 Input!A2:A, Input!D2:E; 
 Input!A2:A, Input!F2:G; 
 Input!A2:A, Input!H2:I; 
 Input!A2:A, Input!J2:K}, "where Col3 is not null", 0)

    0

    =ARRAYFORMULA(QUERY(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(
 QUERY(TRANSPOSE(IF(LEN(Input!A2:A),
 "♦"&Input!A2:A&"♥"&Input!B2:B&"♥"&Input!C2:C&
 "♦"&Input!A2:A&"♥"&Input!D2:D&"♥"&Input!E2:E&
 "♦"&Input!A2:A&"♥"&Input!F2:F&"♥"&Input!G2:G&
 "♦"&Input!A2:A&"♥"&Input!H2:H&"♥"&Input!I2:I&
 "♦"&Input!A2:A&"♥"&Input!J2:J&"♥"&Input!K2:K, )) 
 ,,999^99)),,999^99), "♦")), "♥"), "where Col3 is not null", 0))

    0