# Change duplicate numbers with extensions

#### villival

##### Member
I have an excel list around 500 need to change numbers of any duplicates on the list. For example if there are duplicate part numbers:
1234
1234
1234
I need this changed by adding "A" "B" etc for example :
1234 - A
1234 - B
1234 - C
etc
1234 - AA
1234 - AB
etc.
any solution for this

##### Member
Suppose your data is in A1 and all the way down then try this in B1 and drag down.
=IF(COUNTIF(\$A\$1:\$A\$15,A1)>1,A1&" - "&CHAR(64+COUNTIF(\$A\$1:A1,A1)),"")

#### villival

##### Member
I have an excel list around 500 need to change numbers of any duplicates on the list. For example if there are duplicate part numbers:
1234
1234
1234
I need this changed by adding "A" "B" etc for example :
1234 - A
1234 - B
1234 - C
etc
1234 - AA
1234 - AB
etc.
any solution for this
Thannks for this...

#### p45cal

##### Well-Known Member
If, as you've indicated with suffixes such as AA and AB, there could be more than 26 duplicates of a single number this cumbersome formula will cope with up to 16384 duplicates(!). Assuming your list is in A2:A3000, in any cell in row 2:
Code:
``=A2 & IF(COUNTIF(\$A\$2:\$A\$3000,A2)>1," - " & MID(CELL("address",INDEX(1:1,COUNTIF(A\$2:A2,\$A2))),2,FIND("\$",CELL("address",INDEX(1:1,COUNTIF(A\$2:A2,\$A2))),2)-2),"")``
then copy down.
If you have Office 365 this can be shortened a bit to:
Code:
``=LET(a,A2, b,\$1:\$1,c,A\$2:A2,d,CELL("address",INDEX(b,COUNTIF(c,a))),a & IF(COUNTIF(\$A\$2:\$A\$3000,a)>1," - " & MID(d,2,FIND("\$",d,2)-2),""))``
.
The formulae above do not add any suffix if there is only one instance of a number.
If you're interested this could be converted to a lambda formula (Office 365 only) which could look something like:
=SUFFIX(A2,A\$2:A\$3000)
(no macro or vba user-defined-function needing a macro-enabled workbook)
Come back if you are.

#### bosco_yip

##### Excel Ninja
Another option, formula in cell B2 for letter from A to XFD (being for 16384 duplicated numbers)

then, copied down

#### p45cal

##### Well-Known Member
Another option, formula in cell B2 for letter from A to XFD
Excellent!