find similar data in cells

**hoshyar** · 01-15-2010, 10:39 AM

HI all,

I have a column with 6000 article numbers. I believe that some article numbers are doulbe registered. is it a bit difficult to look at 6000 one by one. is there any way or function that can scan or look at all these 6000 article numbers and show me the double registered ones?

thanks for helping
Hoshyar

**NBVC** · 01-15-2010, 10:43 AM

in an adjacent column...

=If(countif($A$1:$A$6000,A1)>1,"Duplicate","")

copied down...

Adjust ranges to suit

or you can conditionally format

Select Column and go to Format|Conditional format

Select Formula Is and enter:

=Countif($A$1:$A$6000,A1)>1

Click Format and choose from Pattern Tab

**darkyam** · 01-15-2010, 11:21 AM

Or since the range is relatively large, it might be a little more efficient to use =IF(COUNTIF($A$1:$A1,A2),"Duplicate",""), starting in B2. This cuts the number of necessary operations pretty much in half (1,803,000 vs. 3,600,000).