Forum Discussion
Solved
Find duplicates in an range across columns
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?
- 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)
3 Replies
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.
- 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)- Thanks!! The first countif formula was what I was looking for.
