• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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
 

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)

=A2&IF(COUNTIF($A$2:$A$3000,A2)>1," - "&SUBSTITUTE(ADDRESS(1,COUNTIF(A$2:A2,A2),4),"1",""),"")

then, copied down
 

p45cal

Well-Known Member
Wouldn't it be nice if those spending significant time to help others got some feedback?
I, for one, don't usually help again without it.
 
Top