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 ha...
- 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)
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)
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.