Finding duplicate value based on multiple criteria from another column

%3CLINGO-SUB%20id%3D%22lingo-sub-2273570%22%20slang%3D%22en-US%22%3EFinding%20duplicate%20value%20based%20on%20multiple%20criteria%20from%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2273570%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20fellow%20Excel%20users%2C%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20was%20hoping%20I%20would%20be%20able%20to%20find%20some%20support%20here%20with%20a%20formula%20I%20am%20hoping%20to%20find%20that%20solves%20my%20issue.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20want%20to%20find%20duplicates%20in%20one%20column%20(A)%20based%20on%20the%20criteria%20(CA%20or%20US%2C%20NOT%20UK)%20in%20another%20column%20(B)%20and%20I%20want%20the%20result%20to%20show%20in%20column%20C.%20See%20example%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excelfresh_0-1618447883357.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F272953i43573FDF5E7702EF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Excelfresh_0-1618447883357.png%22%20alt%3D%22Excelfresh_0-1618447883357.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20issue%20is%2C%20I%20don't%20want%20to%20see%20a%20duplicate%20if%20that%20duplicate%20is%20only%20present%20within%20US%20(ex.%20row%203%20%26amp%3B%204)%20and%20rather%20only%20if%20the%20duplicate%20is%20found%20both%20in%20US%20and%20CA.%20I%20also%20don't%20want%20to%20a%20duplicate%20to%20pop%20up%20if%20the%20value%20in%20column%20B%20is%20UK%20(ex.%20row%207%20serial%20number%20is%20a%20duplicate%2C%20but%20the%20country%20sale%20is%20UK%20and%20NOT%20CA%20or%20US).%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20help%20would%20be%20appreciated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2273570%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2273770%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20duplicate%20value%20based%20on%20multiple%20criteria%20from%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2273770%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1026589%22%20target%3D%22_blank%22%3E%40Excelfresh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20about%20this%3F%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(AND(COUNTIFS(%24A%242%3A%24A%247%2CA2%2C%24B%242%3A%24B%247%2C%7B%22CA%22%2C%22US%22%7D)%2COR(B2%3D%22CA%22%2CB2%3D%22US%22))%2C%22Y%22%2C%22N%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20place%20the%20formula%20in%20D2%20and%20copy%20it%20down%2C%20it%20will%20produce%20an%20output%20like%20below...%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Duplicate.jpg%22%20style%3D%22width%3A%20923px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F272967i4BFE3C82F5323845%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Duplicate.jpg%22%20alt%3D%22Duplicate.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Visitor

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:

 

Excelfresh_0-1618447883357.png

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. 

1 Reply

@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...

Duplicate.jpg