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
2nd table is the desired output.
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
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", ))}
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", ))}
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")
demo spreadsheet
optimized formula
- Same code behaving differently inside and outside function
- How to use VLOOKUP() + QUERY() + IMPORTRANGE() with multiple conditions?
- How to find value vertically and horizontally, using formula in Sheets?
- Return SUM of all cells that fit TWO CRITERIA related by POSITION
- Sum column until first empty cell
- Extract Data from Multiple Sheets with Similar Names
- How many time based trigger can your add-on run on a document / user?
- ARRAYFORMULA option for checking for cell values on each row of Google Sheet
- Filter a range by array
- Google Sheet Table in one row
- Google Sheets Import CSV popup
- Calculating Average of last 10 Data Entries while Omitting Zeros
- Fastest way to search for a row in a large Google Sheet using/in Google Apps Script
- Google Apps Script showing previous day
- Automatically transfer data from the same cell in different sheet tabs to one table
- How to use the function .getLastRow() in google script
- Updating appscript dropdown in sidebar after a successful operation
- Getting Range not found when using getRange()
- How to generate an uuid in google sheet?
- Improve performance of onEdit simple trigger
- Query and Importrange function with conditions
- Google Apps Script attempting to utilize OAuthScopes is giving an error with no details and prevents saving
- Why can't I format my dates after they are returned from this function?
- Appscript Google sheets returns null
- RegExtract a value from a comma-separated string
- Conditional formatting if a cell is a duplicate value
- Google Sheets Image() function now requires users to hit Allow Access
- Can I use Google Apps Script instead of Formulas for doing calculations on a spreadsheet?
- Is it possible to display an iframe or webview in google sheets
- In the same Google Sheet Workbook, can you extract the info from a cell in Sheet A to a cell in Sheet B, and link it back to the same cell in Sheet A?