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