Automatic Table Extraction

[AustralianExcel]

Hi all! This looks like a great forum, first time coming across it and I've got a question. ?

I am trying to fill the table on the right with the following information (where there is a red 1, 2 and 3):

1. The ticker of the security here - notice how there are two "ETH" values. I would like there to only show 1 occurence of 'ETH' in the table on the right.
2. Quantity of the security here - for ETH, I would like to show 0.12180439 + 0.11669506, and not have a seperate row for each transaction.
3. The total value. This will be pulling the current financial data online to get the current price and multiplying it with the quantity of securities held.

I do not know how to extract data from the table on the left to make the table on the right automatically update to display the information as required above. Thanks for all your help in advance!

 

[Alex Blakenburg]

If you are happy to rely on the dates for any given ticker being later as you go down the rows, then this might work fo you.

20211015 Dashboard Summary.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Decision	Date	Ticker	Qty	Price ($AUD)	Value	Type		Ticker	Qty	Value
3	Buy	5-Aug-21	ETH	0.12180439	3694.4481	450	Crypto		ETH	0.23849945	919.70263
4	Buy	22-Sep-21	ETH	0.11669506	3856.2044	450	Crypto		WEB	1	6.8
5	Buy	20-Aug-21	WEB	234	4.776	1117.58	Stocks
6	Sell	24-Oct-21	WEB	234	6.8	1591.2	Stocks
7	Buy	24-Oct-21	WEB	1	6.8	1591.2	Stocks
8
Sheet1

Cell Formulas
Range	Formula
I3:I4	I3	=UNIQUE(FILTER($C$3:$C$8,$C$3:$C$8<>""),FALSE,FALSE)
J3:J4	J3	=SUMIFS($D$3:$D$8,$C$3:$C$8,I3#,$A$3:$A$8,"Buy")-SUMIFS($D$3:$D$8,$C$3:$C$8,I3#,$A$3:$A$8,"Sell")
K3:K4	K3	=INDEX($E$3:$E$8,XMATCH(I3#,$C$3:$C$8,0,-1),0)*J3#
Dynamic array formulas.