Finding duplicates

[Louja]

How do I write a formula that looks at two columns and looks for
duplicate values in both, and flags the row if it finds them in?

See below example I would want to look at columns A and B and look for
duplicate values in both columns

A B
123 abc
124 def
125 ghi
126 jkl
126 mno
126 mno
127 pqr

 

[Teethless mama]

Assuming your data in A1:B7

In C1: =IF(SUMPRODUCT(($A$1:$A$7=A1)*($B$1:$B$7=B1))>1,"Dup","")
copy down to C7

 

[Ashish Mathur]

Hi,

If by flag you mean, color for identification, then try this. Assume that
your data is in range A11:B17. Click on cell A12 and got to:

Excel 2003: Format > Conditional formatting > Formula is
Excel 2007: Home > Styles > Conditional formatting > new Rule > Use a
formula to determine which cells to format

Enter the following formula
=SUMPRODUCT(($A$11:$A11=$A12)*($B$11:$B11=$B12))>=1. Select the font or
background color and come out of the conditional formatting dialog box. Now
copy and paste the conditional formatting (Copy > Paste special > format)
till B17.

Please note that the above formula will highlight only the occurrences after
the first one. For highlighting all occurrences, try this formula

=SUMPRODUCT(($D$11:$D$17=$D11)*($E$11:$E$17=$E11))>1



--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com