how do i find out unique charecters in a colum of excel 2003
 

i am entering names+a 4 digit no (eg Arun1254) in one colum
can u help me to find the text is reapeted comming rows
my datas are not sorted ..

Col A Col B

Name+ no yes/no
Arun no
vass no
arun yes
raj no
arun yes

which functinn would hepl to come yes/no in Col B

how do i find out unique charecters in a colum of excel 2003
 

Put this in B2 and drag down

=IF(COUNTIF($A$2:$A$20,A2)1,"Yes","No")

Mike

"Arun" wrote:

i am entering names+a 4 digit no (eg Arun1254) in one colum
can u help me to find the text is reapeted comming rows
my datas are not sorted ..

Col A Col B

Name+ no yes/no
Arun no
vass no
arun yes
raj no
arun yes

which functinn would hepl to come yes/no in Col B

how do i find out unique charecters in a colum of excel 2003
 

Hi,
Can someone please tell me why I can't use the following to solve this
problem?
=COUNTIF(LEFT(A1:A10,4),LEFT(A1,4))
Regards - Dave.

"Arun" wrote:

i am entering names+a 4 digit no (eg Arun1254) in one colum
can u help me to find the text is reapeted comming rows
my datas are not sorted ..

Col A Col B

Name+ no yes/no
Arun no
vass no
arun yes
raj no
arun yes

which functinn would hepl to come yes/no in Col B

how do i find out unique charecters in a colum of excel 2003
 

Dave,

Because CountIf doesn't work that way... i.e., it does not process inner
arrays.

But this works:

=SUMPRODUCT(--(LEFT(A1:A10,4)=LEFT(A1,4)))

HTH,
Bernie
MS Excel MVP


"Dave" wrote in message
...
Hi,
Can someone please tell me why I can't use the following to solve this
problem?
=COUNTIF(LEFT(A1:A10,4),LEFT(A1,4))
Regards - Dave.

"Arun" wrote:

i am entering names+a 4 digit no (eg Arun1254) in one colum
can u help me to find the text is reapeted comming rows
my datas are not sorted ..

Col A Col B

Name+ no yes/no
Arun no
vass no
arun yes
raj no
arun yes

which functinn would hepl to come yes/no in Col B

how do i find out unique charecters in a colum of excel 2003
 

Try one of these...

If you have 1000's of rows of data this one may not be the best way to go:

=IF(COUNTIF(A$2:A2,A2)1,"yes","no")

This one is a bit more efficient than the one above if you have 1000's of
rows of data:

=IF(MATCH(A2,A:A,0)=ROW(),"no","yes")

Copy down as needed

--
Biff
Microsoft Excel MVP


"Arun" wrote in message
...
i am entering names+a 4 digit no (eg Arun1254) in one colum
can u help me to find the text is reapeted comming rows
my datas are not sorted ..

Col A Col B

Name+ no yes/no
Arun no
vass no
arun yes
raj no
arun yes

which functinn would hepl to come yes/no in Col B

how do i find out unique charecters in a colum of excel 2003
 

Thanks Bernie,
Countif not processing inner arrays explains a bunch of other issues I've
been having.
Dave.

"Bernie Deitrick" wrote:

Dave,

Because CountIf doesn't work that way... i.e., it does not process inner
arrays.

But this works:

=SUMPRODUCT(--(LEFT(A1:A10,4)=LEFT(A1,4)))

HTH,
Bernie
MS Excel MVP


"Dave" wrote in message
...
Hi,
Can someone please tell me why I can't use the following to solve this
problem?
=COUNTIF(LEFT(A1:A10,4),LEFT(A1,4))
Regards - Dave.

"Arun" wrote:

i am entering names+a 4 digit no (eg Arun1254) in one colum
can u help me to find the text is reapeted comming rows
my datas are not sorted ..

Col A Col B

Name+ no yes/no
Arun no
vass no
arun yes
raj no
arun yes

which functinn would hepl to come yes/no in Col B