SOLVED

# Find duplicates in an range across columns

Occasional Contributor

# 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?

3 Replies
best response confirmed by jaylaplante (Occasional Contributor)
Solution

# Re: Find duplicates in an range across columns

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)

# Re: Find duplicates in an range across columns

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.

# Re: Find duplicates in an range across columns

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