SOLVED

Find duplicates in an range across columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2385417%22%20slang%3D%22en-US%22%3EFind%20duplicates%20in%20an%20range%20across%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2385417%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20working%20with%20a%20large%20data%20set%20at%20work%20and%20am%20looking%20for%20a%20shortcut%20in%20Excel%20to%20identify%20when%20there%20are%20duplicates%20in%20a%20range%20based%20on%20different%20values%20in%20two%20columns.%20Each%20column%20will%20already%20have%20duplicates%2C%20but%20I%20need%20to%20know%20when%20they%20both%20are%20duplicates%20(when%20A1%20%3D%20YES%2FC1%20%3D%205%20and%20A25%20%3D%20YES%2FC25%20%3D%205%2C%20not%20when%20A1%20%3D%20YES%2FC1%20%3D%20YES).%26nbsp%3BIs%20this%20possible%20to%20do%20in%20Excel%20using%20a%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2385417%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2385468%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20duplicates%20in%20an%20range%20across%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2385468%22%20slang%3D%22en-US%22%3Eshort%20answer%2C%20YES%20you%20can%20do%20that.%20How%20do%20you%20want%20the%20output%3F%3CBR%20%2F%3ENew%20list%20with%20only%20unique%20value%20sets%20(for%20non-adjacent%20columns%20I%20and%20K)%3A%20%3DTRANSPOSE(UNIQUE(TRANSPOSE(CHOOSE(%7B1%2C2%7D%2CI1%3AI20%2CK1%3AK20))%2CTRUE))%3CBR%20%2F%3EA%20simple%20true%2Ffalse%20if%20this%20row%20is%20a%20duplicate%3A%3CBR%20%2F%3E%3DCOUNTIFS(%24I%241%3A%24I%2420%2C%20I1%3AI20%2C%24K%241%3A%24K%2420%2CK1%3AK20)%26gt%3B1%3CBR%20%2F%3Ea%20simple%20true%2Ffalse%20if%20this%20row%20is%20unique%20or%20first%20occurrence%20(i.e.%20only%20latter%20duplicates%20are%20false)%3CBR%20%2F%3E%3DCOUNTIFS(%24I%241%3A%24I1%2C%20I1%2C%24K%241%3A%24K1%2CK1)%3D1%20(and%20fill%20down)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2385469%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20duplicates%20in%20an%20range%20across%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2385469%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914627%22%20target%3D%22_blank%22%3E%40jaylaplante%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20(for%20example)%20columns%20A%20to%20C.%20A1%20should%20be%20the%20active%20cell%20in%20the%20selection.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20click%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'%2C%3C%2FP%3E%0A%3CP%3EEnter%20the%20formila%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCOUNTIFS(%24A%3A%24A%2C%24A1%2C%24C%3A%24C%2C%24C1)%26gt%3B1%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20a%20highlight%20color.%3C%2FP%3E%0A%3CP%3EClick%20OK%2Cthen%20click%20OK%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I'm working with a large data set at work and am looking for a shortcut in Excel to identify when there are duplicates in a range based on different values in two columns. Each column will already have duplicates, but I need to know when they both are duplicates (when A1 = YES/C1 = 5 and A25 = YES/C25 = 5, not when A1 = YES/C1 = YES). Is this possible to do in Excel using a formula?

3 Replies
best response confirmed by jaylaplante (New Contributor)
Solution
short answer, YES you can do that. How do you want the output?
New list with only unique value sets (for non-adjacent columns I and K): =TRANSPOSE(UNIQUE(TRANSPOSE(CHOOSE({1,2},I1:I20,K1:K20)),TRUE))
A simple true/false if this row is a duplicate:
=COUNTIFS($I$1:$I$20, I1:I20,$K$1:$K$20,K1:K20)>1
a simple true/false if this row is unique or first occurrence (i.e. only latter duplicates are false)
=COUNTIFS($I$1:$I1, I1,$K$1:$K1,K1)=1 (and fill down)

@jaylaplante 

Select (for example) columns A to C. A1 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format',

Enter the formila

 

=COUNTIFS($A:$A,$A1,$C:$C,$C1)>1

 

Click Format...

Activate the Fill tab.

Select a highlight color.

Click OK,then click OK again.

Thanks!! The first countif formula was what I was looking for.