Trade Summary - Incremental Lookup / counting

[GeeWhiz7]

Hi Gurus,
I've run into another thing I can't quite figure out. In the attached mini-sheet, there are columns with an example date, stock price and buy/sell that takes place.
The columns to the right are what I would like to achieve dynamically where,

• each buy/sell pair equals a trade which is incremented automatically (ie trade 1, 2, 3...) based on the range of input data.
• for each buy/sell pair the buy/sell date and price are captured all in a horizontal table.

I know how to grab things using vlookup, but not how to do things incrementally and I can't quite figure out in VBA how to use "Buy" to initiate the count column...
Thanks for your help.
-Will

TradeSummaryExample.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date	Price	Buy/Sell			Trade #	Buy Date	Sell Date	Buy Price	Sell Price
2	12/1/2020	12.35	Buy			1	12/1/2020	12/7/2020	12.35	14.68
3	12/2/2020	12.21				2	12/9/2020	12/12/2020	13.84	15.99
4	12/3/2020	12.85				3	12/15/2020		14.99
5	12/4/2020	12.01
6	12/5/2020	13.05
7	12/6/2020	13.55
8	12/7/2020	14.68	Sell
9	12/8/2020	13.81
10	12/9/2020	13.84	Buy
11	12/10/2020	15.22
12	12/11/2020	15.78
13	12/12/2020	15.99	Sell
14	12/13/2020	15.25
15	12/14/2020	15.21
16	12/15/2020	14.99	Buy
17	12/16/2020	14.11
18	12/17/2020	14.05
Sheet1

 

[JamesCanale]

MrExcelPlayground5.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date	Price	Buy/Sell			Trade #	Buy Date	Sell Date	Buy Price	Sell Price
2	12/1/2020	12.35	Buy			1	12/1/2020	12/7/2020	12.35	14.68
3	12/2/2020	12.21				2	12/9/2020	12/12/2020	13.84	15.99
4	12/3/2020	12.85				3	12/15/2020		14.99
5	12/4/2020	12.01
6	12/5/2020	13.05
7	12/6/2020	13.55
8	12/7/2020	14.68	Sell
9	12/8/2020	13.81
10	12/9/2020	13.84	Buy
11	12/10/2020	15.22
12	12/11/2020	15.78
13	12/12/2020	15.99	Sell
14	12/13/2020	15.25
15	12/14/2020	15.21
16	12/15/2020	14.99	Buy
17	12/16/2020	14.11
18	12/17/2020	14.05
Sheet23

Cell Formulas
Range	Formula
F2:F4	F2	=SEQUENCE(COUNTIF(Table3[Buy/Sell],"Buy"))
G2:G4	G2	=FILTER(Table3[Date],Table3[Buy/Sell]="Buy","")
H2:H3	H2	=FILTER(Table3[Date],Table3[Buy/Sell]="Sell","")
I2:I4	I2	=FILTER(Table3[Price],Table3[Buy/Sell]="Buy","")
J2:J3	J2	=FILTER(Table3[Price],Table3[Buy/Sell]="Sell","")
Dynamic array formulas.

 

[Fluff]

Another possible option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Date	Price	Buy/Sell			Trade #	Buy Date	Sell Date	Buy Price	Sell Price
2	12/01/2020	12.35	Buy			1	12/01/2020	12/07/2020	12.35	14.68
3	12/02/2020	12.21				2	12/09/2020	12/12/2020	13.84	15.99
4	12/03/2020	12.85				3	15/12/2020		14.99
5	12/04/2020	12.01
6	12/05/2020	13.05
7	12/06/2020	13.55
8	12/07/2020	14.68	Sell
9	12/08/2020	13.81
10	12/09/2020	13.84	Buy
11	12/10/2020	15.22
12	12/11/2020	15.78
13	12/12/2020	15.99	Sell
14	13/12/2020	15.25
15	14/12/2020	15.21
16	15/12/2020	14.99	Buy
17	16/12/2020	14.11
18	17/12/2020	14.05
19
Lists

Cell Formulas
Range	Formula
F2:J4	F2	=LET(f, FILTER(A2:B100,C2:C100<>""),r,ROWS(f),s,SEQUENCE(ROUNDUP(r/2,0),,,2),IFERROR(CHOOSE({1,2,3,4,5},ROUNDUP(s/2,0),INDEX(f,s,1),INDEX(f,s+1,1),INDEX(f,s,2),INDEX(f,s+1,2)),""))
Dynamic array formulas.

Although I'm sure there must be a simpler way than the final choose

 

[GeeWhiz7]

MrExcelPlayground5.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date	Price	Buy/Sell			Trade #	Buy Date	Sell Date	Buy Price	Sell Price
2	12/1/2020	12.35	Buy			1	12/1/2020	12/7/2020	12.35	14.68
3	12/2/2020	12.21				2	12/9/2020	12/12/2020	13.84	15.99
4	12/3/2020	12.85				3	12/15/2020		14.99
5	12/4/2020	12.01
6	12/5/2020	13.05
7	12/6/2020	13.55
8	12/7/2020	14.68	Sell
9	12/8/2020	13.81
10	12/9/2020	13.84	Buy
11	12/10/2020	15.22
12	12/11/2020	15.78
13	12/12/2020	15.99	Sell
14	12/13/2020	15.25
15	12/14/2020	15.21
16	12/15/2020	14.99	Buy
17	12/16/2020	14.11
18	12/17/2020	14.05
Sheet23

Cell Formulas
Range	Formula
F2:F4	F2	=SEQUENCE(COUNTIF(Table3[Buy/Sell],"Buy"))
G2:G4	G2	=FILTER(Table3[Date],Table3[Buy/Sell]="Buy","")
H2:H3	H2	=FILTER(Table3[Date],Table3[Buy/Sell]="Sell","")
I2:I4	I2	=FILTER(Table3[Price],Table3[Buy/Sell]="Buy","")
J2:J3	J2	=FILTER(Table3[Price],Table3[Buy/Sell]="Sell","")
Dynamic array formulas.

This works for what I am trying to do since for my use there will always be a sell paired with a buy for the instrument. I didn't even think of doing it this way before so thank you James!

 

[GeeWhiz7]

Another possible option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Date	Price	Buy/Sell			Trade #	Buy Date	Sell Date	Buy Price	Sell Price
2	12/01/2020	12.35	Buy			1	12/01/2020	12/07/2020	12.35	14.68
3	12/02/2020	12.21				2	12/09/2020	12/12/2020	13.84	15.99
4	12/03/2020	12.85				3	15/12/2020		14.99
5	12/04/2020	12.01
6	12/05/2020	13.05
7	12/06/2020	13.55
8	12/07/2020	14.68	Sell
9	12/08/2020	13.81
10	12/09/2020	13.84	Buy
11	12/10/2020	15.22
12	12/11/2020	15.78
13	12/12/2020	15.99	Sell
14	13/12/2020	15.25
15	14/12/2020	15.21
16	15/12/2020	14.99	Buy
17	16/12/2020	14.11
18	17/12/2020	14.05
19
Lists

Cell Formulas
Range	Formula
F2:J4	F2	=LET(f, FILTER(A2:B100,C2:C100<>""),r,ROWS(f),s,SEQUENCE(ROUNDUP(r/2,0),,,2),IFERROR(CHOOSE({1,2,3,4,5},ROUNDUP(s/2,0),INDEX(f,s,1),INDEX(f,s+1,1),INDEX(f,s,2),INDEX(f,s+1,2)),""))
Dynamic array formulas.

Although I'm sure there must be a simpler way than the final choose

This also works...I guess more than one way to skin the problem. I was trying to loop through the rows with vb and didn't even think of either of these approaches. I suppose if I mix ticker symbols or do something besides long-only trades then I might need to find another way. Thank you Fluff and James.

 

[Fluff]

Glad we could help & thanks for the feedback.

In future please mark the post that has helped you the most as the solution, not your post saying it works, I have changed it for you this time. Thanks

 

[JamesCanale]

If you had to mix in ticker symbols, it would just be another item in the FILTER.

When will I get the LET function?!

 

[Fluff]

When will I get the LET function?!

Assuming you're using Win10 or 11, probably need to ask your IT dept.