Apr 14 2021 05:58 PM
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.
Apr 14 2021 09:20 PM
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...