Same No. - Different Prefix (A to ZZ)

[ken149256]

Hi All, l'm wondering how can l setup the formula for the situation below:
If column A appears more than one times, it will count from A to ZZ.
If just only appears one time, then just shows ZZ.

A B
----------
1 A
1 B
1 C
2 ZZ
3 ZZ
6 ZZ
1 D

Any ideas of how to fix it?
Thanks.

 

[James006]

Hi,

With your example ... what is your expected result ...???

 

[ken149256]

Hi James,

Expected result is on column B.
That's count the no. appears. (From A to ZZ)

Thanks.

 

[James006]

Hi,

In cell B2 ... you can use following formula

SUBSTITUTE(ADDRESS(1,IF(COUNTIF($A$2:$A$10,A2)=1,702,COUNTIF($A$2:A2,A2)),4),"1","")

Hope this will help

 

[ken149256]

Hi James,

It works! Thanks for your coding and reply.
Genius!

 

[James006]

Hi James,

It works! Thanks for your coding and reply.
Genius!

You are welcome ...

Glad the formula is helping you out ...