Forum Discussion
Same No. - Different Prefix (A to ZZ)
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.
3 Replies
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
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.
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.
