How to transform a sequence of numbers w/out logical order

tuui_dm

New Member
Joined
Apr 23, 2020
Messages
4
Platform
  1. Windows
Hi.
I have this database and the first column its the subjects #
The original data goes something like this:

1
1
2
2
2
3
3
3
4
4

Id like to turn it into something like this:

1a
1b
2a
2b
2c
3a
3b
3c
4a
4b

Any tips?
 

Attachments

  • printscreen.PNG
    printscreen.PNG
    5.3 KB · Views: 16

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
You could do something like this. You may have to add some additional letters in the CHOOSE function to accommodate more than 4.
MrExcel20200419a.xlsx
AB
111a
211b
322a
422b
522c
633a
733b
833c
944a
1044b
1155a
1255b
1355c
1455d
1566a
1677a
Sheet14
Cell Formulas
RangeFormula
B1:B16B1=CONCATENATE(A1,CHOOSE(COUNTIF(A$1:A1,A1),"a","b","c","d"))
 
Upvote 0
Hey. Thanks.
It won't work, tho.
I get a "Theres a problem in this formula" dialog box
 
Upvote 0
Welcome to the MrExcel board!

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For up to 26 repeats of the same number, try this

20 04 23.xlsm
AB
111a
211b
322a
422b
522c
622d
722e
822f
922g
1022h
1133a
1233b
1333c
1444a
1544b
1655a
1755b
1855c
1955d
2066a
2177a
Add suffix
Cell Formulas
RangeFormula
B1:B21B1=A1&CHAR(96+COUNTIF(A$1:A1,A1))
 
Last edited:
Upvote 0
Welcome to the MrExcel board!

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For up to 26 repeats of the same number, try this

20 04 23.xlsm
AB
111a
211b
322a
422b
522c
622d
722e
822f
922g
1022h
1133a
1233b
1333c
1444a
1544b
1655a
1755b
1855c
1955d
2066a
2177a
Add suffix
Cell Formulas
RangeFormula
B1:B21B1=A1&CHAR(96+COUNTIF(A$1:A1,A1))
Hi Peter!
Thanks for the tip. Will do the profile update. Actually I just found out about this forum. First time using it.
Your formula worked just fine.
Thank you so much for the help!
<3
 
Upvote 0
You're welcome.

If you happen to ever have more than 26 repeats (up to 16,384), you could also try this one.

20 04 23.xlsm
AB
111a
211b
322a
422b
522c
622d
722e
822f
922g
1022h
1122i
1222j
1322k
1422l
1522m
1622n
1722o
1822p
1922q
2022r
2122s
2222t
2322u
2422v
2522w
2622x
2722y
2822z
2922aa
3022ab
3122ac
3222ad
3322ae
3422af
Add suffix (2)
Cell Formulas
RangeFormula
B1:B34B1=A1&LOWER(SUBSTITUTE(ADDRESS(1,COUNTIF(A$1:A1,A1),4),1,""))
 
Upvote 0
Thanks for the tip on this, Peter. Much appreciated!
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top