google-sheetscountaverageflattengoogle-query-language

How to combine data from multiple rows into a single row based on numbers and certain texts in Google Sheets?


I am creating a journal for trading purposes. In trading, Let's say I bought +1 Share of Disney at $1 and Sold -1 Share of Disney at $2. Now this trade will be shown in two different rows, one row represents that 1 share of Disney has been bought at $1 and the next row shows the trade exit means -1 Share of Disney at $2, now I want to combine the data from the Trade entry row and the Trade exit row in a single row so that it shows both the entry price and exit price with their respective times in a single row.

I need the desired row to show entry time, entry price and exit time and exit price from different rows. Also, the time and the price need to be averaged in case of multiple entries or exits.

This is a bit complex for me, I'd be grateful if you guys could help.

1st table is the raw data

enter image description here

2nd table is the desired output.

enter image description here


Solution

  • first, let's do some fixing... in the data sample, you provided there is an error in cell A11 where the year reads 222 instead of 2022

    enter image description here


    next... in theory, the following formula could work but it's not recommended to use it because the larger the dataset is, the performance wise it will have a toll on your spreadsheet. that said, for the data sample you provided it works:

    ={"Date","Entry Time","Exit Time","Company","Entry Price","Exit Price","Size";
     ARRAYFORMULA(QUERY(QUERY({A2:E, IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0))), 
     (IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), 
     IF(NOT(REGEXMATCH(TO_TEXT(D2:D), "-"))*COUNTIFS(A2:A&C2:C&(IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), A2:A&C2:C&(IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), ROW(X2:X), "<="&ROW(X2:X)), {B2:B, E2:E}, ),  
     (IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))<1)*REGEXMATCH(TO_TEXT(D2:D), "-"), 
     IF(REGEXMATCH(TO_TEXT(D2:D), "-")*COUNTIFS(A2:A&C2:C&(IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))<1)*REGEXMATCH(TO_TEXT(D2:D), "-"), A2:A&C2:C&(IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))<1)*REGEXMATCH(TO_TEXT(D2:D), "-"), ROW(X2:X), "<="&ROW(X2:X)), {B2:B, E2:E}, ), 
     A2:A&C2:C&VLOOKUP(ROW(X2:X), IF(
     IF(A2:A="",,IF(VLOOKUP(C2:C&"×"&COUNTIFS(C2:C, C2:C, ROW(X2:X), "<="&ROW(X2:X))-1, 
     {SPLIT(UNIQUE(C2:C)&"×0♦0", "♦"); C2:C&"×"&COUNTIFS(C2:C, C2:C, ROW(X2:X),"<="&ROW(X2:X)), IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))}, 2, )<>0,,
     IF(NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), COUNTIFS(C2:C&(IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), C2:C&(IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), ROW(X2:X), "<="&ROW(X2:X)), )))<>"", {ROW(X2:X), 
     IF(A2:A="",,IF(VLOOKUP(C2:C&"×"&COUNTIFS(C2:C, C2:C, ROW(X2:X), "<="&ROW(X2:X))-1, 
     {SPLIT(UNIQUE(C2:C)&"×0♦0", "♦"); C2:C&"×"&COUNTIFS(C2:C, C2:C, ROW(X2:X), "<="&ROW(X2:X)), (IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-"))}, 2, )<>0,,
     IF(NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), COUNTIFS(C2:C&(IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), C2:C&(IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), ROW(X2:X), "<="&ROW(X2:X)), )))}), 2)}, 
     "select Col1,avg(Col8),avg(Col11),Col3,avg(Col9),avg(Col12),count(Col11) 
      where Col1 is not null 
      group by Col1,Col3,Col13"), "offset 1", ))}
    

    enter image description here


    instead, it is better to use one helper column (G column) and this formula:

    ={"Date","Entry Time","Exit Time","Company","Entry Price","Exit Price","Size";
     ARRAYFORMULA(QUERY(QUERY({A2:E, G2:G, 
     (G2:G>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), 
     IF(NOT(REGEXMATCH(TO_TEXT(D2:D), "-"))*COUNTIFS(A2:A&C2:C&(G2:G>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), A2:A&C2:C&(G2:G>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), ROW(X2:X), "<="&ROW(X2:X)), {B2:B, E2:E}, ),  
     (G2:G<1)*REGEXMATCH(TO_TEXT(D2:D), "-"), 
     IF(REGEXMATCH(TO_TEXT(D2:D), "-")*COUNTIFS(A2:A&C2:C&(G2:G<1)*REGEXMATCH(TO_TEXT(D2:D), "-"), A2:A&C2:C&(G2:G<1)*REGEXMATCH(TO_TEXT(D2:D), "-"), ROW(X2:X), "<="&ROW(X2:X)), {B2:B, E2:E}, ), 
     A2:A&C2:C&VLOOKUP(ROW(X2:X), IF(
     IF(A2:A="",,IF(VLOOKUP(C2:C&"×"&COUNTIFS(C2:C, C2:C, ROW(X2:X), "<="&ROW(X2:X))-1, 
     {SPLIT(UNIQUE(C2:C)&"×0♦0", "♦"); C2:C&"×"&COUNTIFS(C2:C, C2:C, ROW(X2:X),"<="&ROW(X2:X)), G2:G}, 2, )<>0,,
     IF(NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), COUNTIFS(C2:C&(G2:G>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), C2:C&(G2:G>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), ROW(X2:X), "<="&ROW(X2:X)), )))<>"", {ROW(X2:X), 
     IF(A2:A="",,IF(VLOOKUP(C2:C&"×"&COUNTIFS(C2:C, C2:C, ROW(X2:X), "<="&ROW(X2:X))-1, 
     {SPLIT(UNIQUE(C2:C)&"×0♦0", "♦"); C2:C&"×"&COUNTIFS(C2:C, C2:C, ROW(X2:X), "<="&ROW(X2:X)), (G2:G>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-"))}, 2, )<>0,,
     IF(NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), COUNTIFS(C2:C&(G2:G>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), C2:C&(G2:G>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), ROW(X2:X), "<="&ROW(X2:X)), )))}), 2)}, 
     "select Col1,avg(Col8),avg(Col11),Col3,avg(Col9),avg(Col12),count(Col11) 
      where Col1 is not null 
      group by Col1,Col3,Col13"), "offset 1", ))}
    

    enter image description here


    it took a while for its resetting grouping functionality (column N - with which Erik Tyler helped me with it here) but in its simplicity, it's a mediocre solution if you have a right input:

    =QUERY(A2:N, "select A,avg(I),avg(L),C,avg(J),avg(M),count(L) 
                  where A is not null 
                  group by A,C,N")
    

    enter image description here


    demo spreadsheet

    optimized formula