FIND DUPLICATE

[Guest]

Hi,

I have range of data from which i want to duplicate entries. Data in the
Column D contains a unique ID of 4 digits follows with a name. I want to
find whether the Unique ID repeats in the range. If i use =countif(B:B,B2)>1
it does not work because I want check only the the Unique ID repeats or not.
A B

Date Title
01/12/2006 0489NEROLAC PAIN
01/12/2006 0490Nirma-Super
01/12/2006 0491wrangler
01/12/2006 0492Asian Electi
01/12/2006 0489Acro Paints
01/12/2006 0494NEROLAC PAIN
01/12/2006 0495wrangler
01/12/2006 0496Nirma-Super
01/12/2006 0497NEROLAC PAIN

Thanks

 

[macropod]

Hi shaji,

Assuming your IDs all have four digits, try:
=SUM(IF(LEFT(B$1:B$100,4)=LEFT(B1,4),1,))
entered as an array formula (i.e. input with press <Ctrl>-<Shift>-<Enter> instead of just >-<Enter>) in the first output cell, then
copy down as far as needed. Change B$1:B$100 to suit your input range and B1 to suit the first cell in that range.

Cheers

--
macropod
[MVP - Microsoft Word]


| Hi,
|
| I have range of data from which i want to duplicate entries. Data in the
| Column D contains a unique ID of 4 digits follows with a name. I want to
| find whether the Unique ID repeats in the range. If i use =countif(B:B,B2)>1
| it does not work because I want check only the the Unique ID repeats or not.
| A B
|
| Date Title
| 01/12/2006 0489NEROLAC PAIN
| 01/12/2006 0490Nirma-Super
| 01/12/2006 0491wrangler
| 01/12/2006 0492Asian Electi
| 01/12/2006 0489Acro Paints
| 01/12/2006 0494NEROLAC PAIN
| 01/12/2006 0495wrangler
| 01/12/2006 0496Nirma-Super
| 01/12/2006 0497NEROLAC PAIN
|
| Thanks
|
|

 

[Guest]

Another way ..
In C2: =LEFT(B2,4)
In D2: =IF(C2="","",IF(COUNTIF($C$2:C2,C2)>1,"Dup",""))
Select C22, copy down as far as required
Col D will return "Dup" for any duplicates found

 

[Guest]

=SUMPRODUCT(--(LEFT($B$1:$B$9,4)=LEFT(B1,4)))