Forum Discussion

dbhk852's avatar
dbhk852
Copper Contributor
Mar 16, 2021

Creating a separate table based on two criteria in excel

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.




BI & Data Analysis
Charting
excel
Formulas and Functions

3 Replies

  • tauqeeracma's avatar
    tauqeeracma
    Iron Contributor
    Mar 16, 2021

    HIdbhk852 

    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

     

     

    • dbhk852's avatar
      dbhk852
      Copper Contributor
      Mar 16, 2021

      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

Resources