Forum Discussion

Excelfresh's avatar
Excelfresh
Copper Contributor
Apr 15, 2021

Finding duplicate value based on multiple criteria from another column

Hello fellow Excel users, 

I was hoping I would be able to find some support here with a formula I am hoping to find that solves my issue. 

I want to find duplicates in one column (A) based on the criteria (CA or US, NOT UK) in another column (B) and I want the result to show in column C. See example below:

 

The issue is, I don't want to see a duplicate if that duplicate is only present within US (ex. row 3 & 4) and rather only if the duplicate is found both in US and CA. I also don't want to a duplicate to pop up if the value in column B is UK (ex. row 7 serial number is a duplicate, but the country sale is UK and NOT CA or US). 

Any help would be appreciated. 

excel
Formulas and Functions

1 Reply

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Apr 15, 2021

    Excelfresh 

     

    How about this?

    =IF(AND(COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,{"CA","US"}),OR(B2="CA",B2="US")),"Y","N")

     

    If you place the formula in D2 and copy it down, it will produce an output like below...

     

     

Resources