if-statementgoogle-sheetsgoogle-sheets-formulaflattenfirst-normal-form

How to create new rows of data based on a single cell within an arrayforumla-wrapped-query call in google sheets?s?

I currently have a dataset that is formatted:

Company Clients Date Type Amt
comp1 client1, client2, client3 01/02/22 visa $1500
comp2 client1 amex $600
comp3 client3, client4, client5, client1 02/23/22 check $4000
comp4 client6, client7, client8 check $1800

And I would like to end up with a dataset formatted for each client transaction:

Client Date Type Amt Company Expense type
client1 01/02/22 visa $500 comp1 Company
client2 01/02/22 visa $500 comp1 Company
client3 01/02/22 visa $500 comp1 Company
client1 amex $600 comp2 Company
client3 02/23/22 check $1000 comp3 Company
client4 02/23/22 check $1000 comp3 Company
client5 02/23/22 check $1000 comp3 Company
client1 02/23/22 check $1000 comp3 Company
client6 check $600 comp4 Company
client7 check $600 comp4 Company
client8 check $600 comp4 Company

Thanks to user player0 for pointing me in the right direction with:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(IFERROR(SPLIT(B1:B, ","))="",,
 SPLIT(B1:B, ", ", )&"​"&C1:C&"​"&D1:D&"​"&E1:E/LEN(SUBSTITUTE(FLATTEN(
 QUERY(TRANSPOSE(IFERROR(1/(1/(SPLIT(B1:B, ",")<>"")))),,9^9)), " ", ))&"​"&A1:A)), "​"), 
 "where Col3 is not null format Col2'mm/dd/yy', Col4'$0'", ))

This comes very close to accomplishing what I'm trying to do, but shifts cells when there are missing values so the end result looks like:

Client Date Type Amt Company Expense type
client1 01/02/22 visa $500 comp1
client2 01/02/22 visa $500 comp1
client3 01/02/22 visa $500 comp1
client1 amex $600 comp2
client3 02/23/22 check $1000 comp3
client4 02/23/22 check $1000 comp3
client5 02/23/22 check $1000 comp3
client1 02/23/22 check $1000 comp3
client6 check $600 comp4
client7 check $600 comp4
client8 check $600 comp4

To work around this I added an if statement for each column call that checks if its empty and fills in with a space if it is empty. This gets things in the correct order, but there may be a more efficient way? The last thing to do is populate the final column with the broad category of "company", basically populate all cells in col 6 to equal the header from col 1 in the original table. This new formula looks like this:


    =ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(IFERROR(SPLIT(B2:B, ","))=""
    ,,SPLIT(B2:B, ",",)&"​"&IF(C2:C<>"",C2:C," ")&"​
    "&IF(!D2:D<>"",!D2:D," ")&"​  
    "&IF(!E2:E<>"",!E2:E/LEN(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(
    IFERROR(1/(1/(SPLIT(!B2:B,",")<>"")))),,9^9))," ",))," ")&"​   
    "&IF(!A2:A<>"",!A2:A," ")&"
    "&!A1)), "​"),
    "Where Col5 is not null format Col2'mm/dd/yy', Col5'$0'", 0))

This generates the final field of "Company" but puts it appended to the previous column. I believe this is happening because ARRAYFORMULA wants to be working with ranges of the same size and so interprets this as a string concatenation since it can't operate on the range? If anyone has other thoughts as to why please let me know!

Client Date Type Amt Company Expense type
client1 01/02/22 visa $500 comp1Company
client2 01/02/22 visa $500 comp1Company
client3 01/02/22 visa $500 comp1Company
client1 amex $600 comp2Company
client3 02/23/22 check $1000 comp3Company
client4 02/23/22 check $1000 comp3Company
client5 02/23/22 check $1000 comp3Company
client1 02/23/22 check $1000 comp3Company
client6 check $600 comp4Company
client7 check $600 comp4Company
client8 check $600 comp4Company
Solution

  • use:

    =ARRAYFORMULA(QUERY(SPLIT(FLATTEN(IF(IFERROR(SPLIT(B1:B, ","))="",,
 SPLIT(B1:B, ", ", )&"​"&C1:C&"​"&D1:D&"​"&E1:E/LEN(SUBSTITUTE(FLATTEN(
 QUERY(TRANSPOSE(IFERROR(1/(1/(SPLIT(B1:B, ",")<>"")))),,9^9)), " ", ))&"​"&A1:A)), "​"), 
 "where Col3 is not null format Col2'mm/dd/yy', Col4'$0'", ))

    enter image description here