Finding duplicates

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

Assuming your data in A1:B7

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


"Louja" wrote:

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
.

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

"Louja" wrote in message
...
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