Find duplicates in an range across columns

[jaylaplante]

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?

[#2 · Accepted 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)

[#3]

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

[#4]

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