Finding duplicates in one column against unique description in another column

**Notlob** · 08-08-2009, 04:58 AM

Hello all,

I am using using excel to track assets. I have two columns titled Description and Serial Number. I wish to only highlight duplicate serial numbers where the Description is the same. At present I am using CF to highlight duplicate serial numbers using =COUNTIF($B$2:$B$11,B2)>1. This highlights all duplicate serial numbers but I wish to refine it only to show duplicates against its description.

Thanks

**martindwilson** · 08-08-2009, 05:13 AM

try
=SUMPRODUCT(($A$2:$A$12=$A2)*($B$2:$B$12=$B2))>1
you might want to include
=SUMPRODUCT(($A$2:$A$12=$A2)*($B$2:$B$12=$B2)*($A$2:$A$12<>""))>1 so as not to highlight blanks

**DonkeyOte** · 08-08-2009, 06:02 AM

An alternative given you're using CF (and CF formulae are processed as arrays by default):

=AND(COUNTA(B2:C2)=2,ISNUMBER(MATCH($B2&$C2,$B$1:$B1&$C$1:$C1,0)))

(assumes serials in B and Descriptions in C, and 2nd instance onwards of match to be highlighted - unclear)