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

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]


"shaji" wrote in message ...
| 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
|
|

Another way ..
In C2: =LEFT(B2,4)
In D2: =IF(C2="","",IF(COUNTIF($C$2:C2,C2)1,"Dup",""))
Select C2:D2, copy down as far as required
Col D will return "Dup" for any duplicates found
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"shaji" wrote:
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

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

"shaji" wrote:

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