Help matching several fields then filtering

[kfaz81]

I have a list of accounts with associated purchases and sales that I need to associate with the category of the item purchased or sold, and then filter to only have those bought and sold within the same category remain. 

For example, account #1 sold "N" and bought "Y". Items "N" and "Y" are of the same category - "Small". In contrast, account #3 sold "C" and bought "B", items of different categories.

I'm not sure what tool or what workflow would easily accomplish this for me.

Thank you for your help!

Buy Acct #	Buy Action	Buy ID	Buy Trade Date	Sell Acct #	Sell Action	Sell ID	Sell Trade Date
1	Buy	Y	02/28/2020	1	Sell	N	02/28/2020
2	Buy	H	03/09/2020	2	Sell	AB	03/09/2020
3	Buy	B	04/28/2020	3	Sell	C	04/28/2020
4	Buy	A	03/17/2020	4	Sell	B	03/17/2020
5	Buy	B	04/06/2020	5	Sell	D	04/06/2020

Category	Primary ID	Secondary ID	Tertiary ID
Large	C	L	W
Value	E	M	X
Small	A	N	Y
Total	F	O	Z
Equity	G	P
International	D	Q	AA
Market	H	R	AB
Bond	B	S	AC
Corporate	I	T
US	J	U
Debt	K	V

[Per · Accepted solution]

Hi,

I have attached a solution based on how I understood the issue. Transpose the categorization and then join the categories for each buy and sell. Last filter out the non-matching values. Hope it makes sense and solves your issue!

[kfaz81]

This worked perfect, thank you!