Convert Google Sheet formula to Excel

[mr_alok]

How can I convert the following Google Sheet formula to Excel?
Iferror(index(arrayformula(filter(TX!L:L,TX!C:C<>"",row(TX!C:C)=max(if(TX!C:C=B2,row(TX!C:C),0)))) ,1),0)

 

[Fluff]

Hi & welcome to MrExcel.
Can you post some sample data, along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[mr_alok]

Hi & welcome to MrExcel.
Can you post some sample data, along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks for guiding. I've attached the sheet below.

In Portfolio sheet, I need Cumulative Value (column M) and Cumulative Cost (Column R) for each ticker from Tx sheet.
e.g. for AAPL, the last transaction is in Row 8, as such Cumulative Value for AAPL is 13 (M8), and Cumulative Cost is 2673.15 (R8)
for NVDA, the last transaction is in Row 7, as such the Cumulative Value for NVDA is 5 (M7), and CUmulative Cost is 2537.15 (M7)

In case my Tx grows in future with other stocks, how can I pull all the unique Ticker symbols from Tx sheet to Portfolio sheet.
e.g. If I add a new row as MSFT in Tx, the ticker should appear in B4 together with respective Cumulative Values and Cumulative Cost.

Portfolio.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	No	Exchange	Date	Ticker	Trading Pair	Tx Type	Units	Price per Unit	Fee	Fee Currency	Prev Row	Prev Units	Cumulative Units	Transacted Value	Previous Cost	Transaction Cost	Transaction Cost (per unit)	Cumulative Cost	Gain/Loss fromSale
2	1	NYSE	08/03/2021	AAPL	USD	BUY	15.00	116.83	4.00	USD	0	0.00	15.00	1752.45	0.00	-	-	1752.45	0.00
3	2	NYSE	09/03/2021	NVDA	USD	BUY	10.00	494.43	4.00	USD	0	0.00	10.00	4944.30	0.00	-	-	4944.30	0.00
4	3	NYSE	18/03/2021	NVDA	USD	BUY	10.00	520.43	4.00	USD	3	10.00	20.00	5204.30	4944.30	-	-	10148.60	0.00
5	4	NYSE	19/03/2021	AAPL	USD	BUY	20.00	120.21	4.00	USD	2	15.00	35.00	2404.20	1752.45	-	-	4156.65	0.00
6	5	NYSE	22/03/2021	AAPL	USD	SELL	25.00	123.79	4.00	USD	5	35.00	10.00	3094.75	4156.65	2969.04	118.76	1187.61	125.71
7	6	NYSE	22/03/2021	NVDA	USD	SELL	15.00	535.39	4.00	USD	4	20.00	5.00	8030.85	10148.60	7611.45	507.43	2537.15	419.40
8	7	NYSE	25/03/2021	AAPL	USD	BUY	3.00	495.18	4.00	USD	6	10.00	13.00	1485.54	1187.61	-	-	2673.15	0.00
Tx

Portfolio.xlsx
	A	B	C	D
1	S.No	Ticker	Cumulative Units	Cumulative Cost
2	1	AAPL	13.00	2673.15
3	2	NVDA	5.00	2537.15
Portfolio

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D
1	S.No	Ticker	Cumulative Units	Cumulative Cost
2	1	AAPL	13	2673.154286
3	2	NVDA	5	2537.15
4
5
6
Data

Cell Formulas
Range	Formula
B2:B6	B2	=IFERROR(INDEX(TX!$D$2:$D$100,AGGREGATE(15,6,(ROW(TX!$D$2:$D$100)-ROW(TX!$D$2)+1)/(TX!$D$2:$D$100<>"")/(ISNA(MATCH(TX!$D$2:$D$100,B$1:B1,0))),1)),"")
C2:C6	C2	=IF(B2="","",INDEX(TX!$M$2:$M$100,AGGREGATE(14,6,(ROW(TX!$D$2:$D$100)-ROW(TX!$D$2)+1)/(TX!$D$2:$D$100=B2),1)))
D2:D6	D2	=IF(B2="","",INDEX(TX!$R$2:$R$100,AGGREGATE(14,6,(ROW(TX!$D$2:$D$100)-ROW(TX!$D$2)+1)/(TX!$D$2:$D$100=B2),1)))

 

[mr_alok]

Thanks @Fluff it works!
How about if I want to sort column B (portfolio tab) based on Ticker symbols?

 

[Fluff]

I'm afraid I don't know how to do that with the 2016 version of Excel.