1

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
player0
  • 124,011
  • 12
  • 67
  • 124
Kent Ratliff
  • 13
  • 5
  • Does this answer your question? [How to create a formula that will repeat a range by splitting one column and joining with the other column?](https://stackoverflow.com/questions/74065157/how-to-create-a-formula-that-will-repeat-a-range-by-splitting-one-column-and-joi) – TheMaster Oct 25 '22 at 13:19

2 Answers2

0

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • That almost works! Here is a cleaned up version of the data I'm working with. [link](https://docs.google.com/spreadsheets/d/1Nngf-r115CTt---BHI80CkpxlnnL_NFrBHnUOnjOCGo/edit#gid=77843647) data types for columns I'm not using. Its not uniform, there are a lot of null values. You can see in the your edited Query in the "Reporting formatted" tab. I believe I understand your formula, and think its "shifting" data 1 cell to the left where there are null values in the initial IF statement that compiles all of the cells for the Query range. Is there a way to make this IF statement keep null values? – Kent Ratliff Oct 25 '22 at 18:41
  • Edited the original dataset to include null values and has the same effect, cells shifted – Kent Ratliff Oct 25 '22 at 18:53
0

Best I could come up with was to leave that column out of the QUERY and just have it populate IF the client is not empty, worked well enough. Still curious if there's a simple way to create a repeating array in a query

Kent Ratliff
  • 13
  • 5
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 17 '22 at 04:36