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
- Downloading a PDF version of the open spreadsheet in Google Drive via scripts
- exporting spreadsheet to pdf then saving the file in google drive
- How to get last number in a row on Google Spreadsheets
- A Script to Simplify Creating a SO Table
- Archive a row after a dropdown status is selected on multiple sheets
- How to extract URL from Link in Google Sheets using a formula?
- Create list of dates in Google Sheets based on day-of-month schedule
- Get Google Sheet by ID?
- Return Column Based on Column Headers
- Add title row with ARRAYFORMULA in Google Sheets
- Why does my Google Service Account not see files shared with it, but can see files created with it?
- Google Sheets macro to copy a line of data to next empty row on another sheet
- Google Sheets - Auto border to a Dynamic Data
- Fix Google Sheets formula so it will append the header title of each column it is pulling data from, instead of just the first column's title
- VLOOKUP IMPORTRANGE to select the column number in another file also
- Split cell with dates and find matching dates on headers
- How to get the next Bi-weekly date based on a given start date and today's date in Google Sheets
- How can I determine if a respondent's email address is unique in a list?
- ARRAYFORMULA in GOOGLE SHEETS
- Can a google-apps-script be written to download a csv file from spreadsheet directly to a local hard drive with an onEdit trigger?
- API call to directory.members.insert failed with error: Resource Not Found: groupKey
- How to find common words in two columns in Google Sheets
- Get list of unique words without numbers and symbols from paragraph range in Google Sheet?
- Apps Script Delete Image
- App Script to download specific cells in Google Sheet to PDF
- Export Google Docs comments into Google Sheets, along with highlighted text?
- INDIRECT function in an INDEX-MATCH-MATCH
- How to write to a "Google Spreadsheet" from Excel 2003 VBA
- How to use date with LIKE inside query?
- Know if the cell is being used in any formula