Count Duplicates in text values
 

I have 19 digit text values in column A from A2 to A501.
Col A Col B
8991151420000560945 1
8991151420000560721 1
8991151420000560770 1
8991151420000560770 2
8991151420000560861 1
8991151420000560929 1
8991151420000561125 1
and so on.

I need to count duplicate text for each cell to be desplayed in
adjecent cell (Col B).

Need help with the formula as countif is not working in this
situation.

Regards,
Madiya

Count Duplicates in text values
 

Hi Madiya,

Put following formula in cell B2 and then drag

=COUNTIF($A$2:A2,A2)


H S Shastri


++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++



"Madiya" wrote:

I have 19 digit text values in column A from A2 to A501.
Col A Col B
8991151420000560945 1
8991151420000560721 1
8991151420000560770 1
8991151420000560770 2
8991151420000560861 1
8991151420000560929 1
8991151420000561125 1
and so on.

I need to count duplicate text for each cell to be desplayed in
adjecent cell (Col B).

Need help with the formula as countif is not working in this
situation.

Regards,
Madiya

Count Duplicates in text values
 

Madiya,

Try using an anchored range:

=COUNTIF($A$2:A2,A2)

copied down.

HTH,
Bernie
MS Excel MVP




"Madiya" wrote in message
...
I have 19 digit text values in column A from A2 to A501.
Col A Col B
8991151420000560945 1
8991151420000560721 1
8991151420000560770 1
8991151420000560770 2
8991151420000560861 1
8991151420000560929 1
8991151420000561125 1
and so on.

I need to count duplicate text for each cell to be desplayed in
adjecent cell (Col B).

Need help with the formula as countif is not working in this
situation.

Regards,
Madiya

Count Duplicates in text values
 

Hi Madiya
Try this: =SUMPRODUCT(--($A$2:$A$501=A2))
HTH
John
"Madiya" wrote in message
...
I have 19 digit text values in column A from A2 to A501.
Col A Col B
8991151420000560945 1
8991151420000560721 1
8991151420000560770 1
8991151420000560770 2
8991151420000560861 1
8991151420000560929 1
8991151420000561125 1
and so on.

I need to count duplicate text for each cell to be desplayed in
adjecent cell (Col B).

Need help with the formula as countif is not working in this
situation.

Regards,
Madiya

Count Duplicates in text values
 

Hi,

This depends on what you mean by duplicates, but I suspect this is what you
want:

=COUNTIF(A$2:A2,A2)
but the other option is:
=COUNTIF(A$2:A$501,A2)

In 2007 you can conditionally format to mark duplicates of the formula II
type and you can issue a new command to remove duplicates as defind in
formula I above.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Madiya" wrote:

I have 19 digit text values in column A from A2 to A501.
Col A Col B
8991151420000560945 1
8991151420000560721 1
8991151420000560770 1
8991151420000560770 2
8991151420000560861 1
8991151420000560929 1
8991151420000561125 1
and so on.

I need to count duplicate text for each cell to be desplayed in
adjecent cell (Col B).

Need help with the formula as countif is not working in this
situation.

Regards,
Madiya

Count Duplicates in text values
 

On Feb 16, 8:45*pm, Shane Devenshire
wrote:
Hi,

This depends on what you mean by duplicates, but I suspect this is what you
want:

=COUNTIF(A$2:A2,A2)
but the other option is:
=COUNTIF(A$2:A$501,A2)

In 2007 you can conditionally format to mark duplicates of the formula II
type and you can issue a new command to remove duplicates as defind in
formula I above.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire



"Madiya" wrote:
I have 19 digit text values in column A from A2 to A501.
Col A * * * * * * * * * * * * * * * * * * Col B
8991151420000560945 * * * * * * 1
8991151420000560721 * * * * * * 1
8991151420000560770 * * * * * * 1
8991151420000560770 * * * * * * 2
8991151420000560861 * * * * * * 1
8991151420000560929 * * * * * * 1
8991151420000561125 * * * * * * 1
and so on.

I need to count duplicate text for each cell to be desplayed in
adjecent cell (Col B).

Need help with the formula as countif is not working in this
situation.

Regards,
Madiya- Hide quoted text -

- Show quoted text -

Hi all,
Thanks for your response.
I have alrady tried =COUNTIF(A$2:A$501,A2) and other variance.
Since the text is a 19 digit value, I think excel goofs up the
calculations and shows wrong result.
Thats the reason I have posted here to get help.

Jhon,
Your formula =SUMPRODUCT(--($A$2:$A$501=A2)) works fine and results
are as expected.

Thanks aagain for all of your help and time.

Regards,
Madiya

Count Duplicates in text values
 

You're welcome
John
"Madiya" wrote in message
...
On Feb 16, 8:45 pm, Shane Devenshire
wrote:
Hi,

This depends on what you mean by duplicates, but I suspect this is what you
want:

=COUNTIF(A$2:A2,A2)
but the other option is:
=COUNTIF(A$2:A$501,A2)

In 2007 you can conditionally format to mark duplicates of the formula II
type and you can issue a new command to remove duplicates as defind in
formula I above.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire



"Madiya" wrote:
I have 19 digit text values in column A from A2 to A501.
Col A Col B
8991151420000560945 1
8991151420000560721 1
8991151420000560770 1
8991151420000560770 2
8991151420000560861 1
8991151420000560929 1
8991151420000561125 1
and so on.

I need to count duplicate text for each cell to be desplayed in
adjecent cell (Col B).

Need help with the formula as countif is not working in this
situation.

Regards,
Madiya- Hide quoted text -

- Show quoted text -

Hi all,
Thanks for your response.
I have alrady tried =COUNTIF(A$2:A$501,A2) and other variance.
Since the text is a 19 digit value, I think excel goofs up the
calculations and shows wrong result.
Thats the reason I have posted here to get help.

Jhon,
Your formula =SUMPRODUCT(--($A$2:$A$501=A2)) works fine and results
are as expected.

Thanks aagain for all of your help and time.

Regards,
Madiya