Identifying Multiple Duplicates Based on ID Number

**Jakz34** · 03-28-2011, 04:39 PM

Is there any way to identify multiple duplicates based on a specific number? I have attached an example that has generic ID numbers and bonuses for each ID number with a 3 letter bonus code and 3 number bonus code. If both the letter and number bonus match per ID, I want those identified some way (highlighting was used for identification in the file).

Could this be done with Excel function or is VBA needed?

Thanks!

**Blake 7** · 03-28-2011, 06:22 PM

As per your sample

in cell D2

Please Login or Register  to view this content.

then you can use the following formula in Conditional Formatting to highlight only the first occurrence of an entry in the list.

Please Login or Register  to view this content.

or

You can also do the reverse of this with Conditional Formatting. Using the formula below in Conditional Formatting will highlight only the second and subsequent occurrences of a value. The first occurrence of the value will not be highlighted.

Please Login or Register  to view this content.

**scottylad2** · 03-28-2011, 07:22 PM

=SUMPRODUCT(--(($A$2:$A$14&$B$2:$B$14&$C$2:$C$14=A2&B2&C2))) will give you a 1 for non dup and a 2 for duplicates as an alternative to Blae 7

or

if you'd prefer the word Dup
IF(SUMPRODUCT(--(($A$2:$A$14&$B$2:$B$14&$C$2:$C$14=A2&B2&C2)))>1,"dup",SUMPRODUCT(--(($A$2:$A$14&$B$2:$B$14&$C$2:$C$14=A2&B2&C2))))

**scottylad2** · 03-28-2011, 07:34 PM

=SUMPRODUCT(--(($A$2:$A$14&$B$2:$B$14&$C$2:$C$14=$A2&$B2&$C2)))=2 in Conditional Formating, highlight all your cells that need evaluating as per your sample and this formula will highlight only the duplicates. Dollar signs need to be as my example and the row will highlight on each instance

**Helal** · 10-03-2012, 05:46 PM

The formula worked to identify dups. But how do I assign 1 to the first occurence of a dup ID and 0 to the second, third, fourth, etc...occurrences of the dup ID?