Making a duplicate filter for 2 columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2298919%22%20slang%3D%22en-US%22%3EMaking%20a%20duplicate%20filter%20for%202%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2298919%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20apply%20a%20macro%20to%20find%20duplicates%20between%20two%20columns%2C%20B%20and%20C.%20My%20current%20formula%20is%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ISERROR(MATCH(TRIM(B2)%2C%24C%242%3A%24C%24229%2C0))%2C%22Unique%22%2C%22Duplicate%22)%3C%2FP%3E%3CP%3EWhen%20applied%20in%20a%20placeholder%20column%2C%20the%20macro%20does%20not%20work%20properly%20(For%20example%201000195%20is%20a%20duplicate%20but%20shows%20up%20as%20unique).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20solutions%20to%20resolve%20this%20problem.%20Also%20there%20are%20some%20duplicates%20within%20column%20B%20that%20I%20am%20trying%20to%20keep%20in%20the%20spreadsheet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKurtis%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2298919%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2299167%22%20slang%3D%22en-US%22%3ERe%3A%20Making%20a%20duplicate%20filter%20for%202%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2299167%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037020%22%20target%3D%22_blank%22%3E%40kadam062%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%201000195%20in%20column%20B%20is%20part%20of%20a%20%3CSTRONG%3Estring%3C%2FSTRONG%3E%2C%20but%20the%201000195%20in%20column%20C%20is%20a%20%3CSTRONG%3Enumber%3C%2FSTRONG%3E.%20They%20are%20not%20the%20same!%20Change%20the%20formula%20in%20D2%20to%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(ISERROR(MATCH(TRIM(B2)%2C%24C%242%3A%24C%24229%26amp%3B%22%22%2C0))%2C%22Unique%22%2C%22Duplicate%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eand%20confirm%20with%20Ctrl%2BShift%2BEnter%2C%20then%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello, 

 

I am trying to apply a macro to find duplicates between two columns, B and C. My current formula is 

=IF(ISERROR(MATCH(TRIM(B2),$C$2:$C$229,0)),"Unique","Duplicate")

When applied in a placeholder column, the macro does not work properly (For example 1000195 is a duplicate but shows up as unique).

 

Any solutions to resolve this problem. Also there are some duplicates within column B that I am trying to keep in the spreadsheet. 

 

Thanks, 

 

Kurtis

2 Replies

@kadam062 

The 1000195 in column B is part of a string, but the 1000195 in column C is a number. They are not the same! Change the formula in D2 to

 

=IF(ISERROR(MATCH(TRIM(B2),$C$2:$C$229&"",0)),"Unique","Duplicate")

 

and confirm with Ctrl+Shift+Enter, then fill down.

Worked like a charm. Thanks!