Creating a separate table based on two criteria in excel

[dbhk852]

I am hoping someone can assist with the below example. 

I have a list of stock orders which are unique and can (in this perfect world model) either be an order to BUY or SELL. Ideally I would like to create a new table containing those Stocks where I have both BUY and Sell orders. However I am also happy just to have a column added to the end that just says "Potential Cross" or some equivalent. 

Or Ideally a separate table like this:-

Essentially there is two parts to it, the names in the first column need to match, but then the side in the second column needs to not match.

I am sure there is an easy way of doing this, but I'm lost.

Many Thanks.

[#2]

HI@dbhk852 

If you are OK with below slightly different presentation you may use this formula 

=IF(AND(COUNTIFS($C$5:$C$23,I5,$D$5:$D$23,"Buy")>0,COUNTIFS($C$5:$C$23,I5,$D$5:$D$23,"Sell")>0),SUMIFS($E$5:$E$23,$C$5:$C$23,I5,$D$5:$D$23,"Buy"),"")

A sample file is also attached for your reference, please let me know if it works for you.

Thanks

Tauqeer

[#3]

@tauqeeracma 

Hi Tauqeer,

Thank you very much for the quick response, thats really helpful. And yep it works for my purposes. 

Really appreciate the help, was chasing my tail for a while there.

All the best

[#4]

@dbhk852 

You are welcome