=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