Same No. - Different Prefix (A to ZZ)

[Tsz hin WONG]

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.

[#2]

Hi,

That could be

=IF(COUNTIF($A$1:$A$100,A1)-1,IF(INT((COUNTIF($A$1:$A1,A1)-1)/26)=0,"",CHAR(INT((COUNTIF($A$1:$A1,A1)-1)/26)+65))&CHAR(MOD(COUNTIF($A$1:$A1,A1)-1,26)+65),"ZZ")

Please see attached

[#3]

Hello Sergei

My understanding is that the order would be A, B, C .... X, Y, Z, AA, AB, ...

Inspired by your solution I came up with a slightly different one.

See attached file.

[#4]

Hi Detlef,

Yes, you are right and your formula is shorter. Based on it one more variant without CHAR

=IF(COUNTIF($A$1:$A$100,A2)-1,SUBSTITUTE(INDEX(ADDRESS(1,COLUMN($1:$702),4),0,COUNTIF($A$1:$A2,A2)),1,""),"ZZ")

702 is column ZZ and I hope Excel has right order of columns.

I collected all 3 variants in one file.