Locating duplicate entries using specific criteria

  • Thread starter Thread starter Kim
  • Start date Start date
K

Kim

I have a spreadsheet with multiple columns. I want to
write a formula that will identify a duplicate entry based
on the satisfaction of specific criteria (i.e., same
payment amount, same adjuster name & same invoice
number). Does anyone know how to create a formula to do
this?
 
Assuming headers in row 1 and the columns are A,B,C, then
try this in row 2 in an open column:

=SUMPRODUCT(--(A2&B2&C2=$A$2:$A$10&$B$2:$B$10&$C$2:$C$10))
1
Click to expand...

and fill down to row 10. All cells showing TRUE indicates
a duplicate exists.

Or just remove the ">1" from the formula to see the number
of identical entries for that specific combination.

HTH
Jason
Atlanta, GA
 
Assuming headers in row 1 and the columns are A,B,C, then
try this in row 2 in an open column:

=SUMPRODUCT(--(A2&B2&C2=$A$2:$A$10&$B$2:$B$10&$C$2:$C$10))>1
Click to expand...

A B C
- - -
fo oo oo
foo oo o
fooo o o
f o oooo
12 34 56
123 45 6
1 23 456
etc.

For general text or numbers, concatenation is begging for errors. *ALWAYS*
compare each column separately unless it's specifically the concatenated colums
you want to check.

=SUMPRODUCT((A2=$A$2:$A$10)*(B2=$B$2:$B$10)*(C2=$C$2:$C$10))>1
 
Thanks Jason and Harlan.
-----Original Message-----
... (A2&B2&C2=$A$2:$A$10&$B$2:$B$10&$C$2:$C$10))>1

A B C
- - -
fo oo oo
foo oo o
fooo o o
f o oooo
12 34 56
123 45 6
1 23 456
etc.

For general text or numbers, concatenation is begging for errors. *ALWAYS*
compare each column separately unless it's specifically the concatenated colums
you want to check.

=SUMPRODUCT((A2=$A$2:$A$10)*(B2=$B$2:$B$10)* (C2=$C$2:$C$10))>1
Click to expand...
 
Back
Top