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

[tuui_dm]

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?

 

[KRice]

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
	A	B
1	1	1a
2	1	1b
3	2	2a
4	2	2b
5	2	2c
6	3	3a
7	3	3b
8	3	3c
9	4	4a
10	4	4b
11	5	5a
12	5	5b
13	5	5c
14	5	5d
15	6	6a
16	7	7a
Sheet14

Cell Formulas
Range	Formula
B1:B16	B1	=CONCATENATE(A1,CHOOSE(COUNTIF(A$1:A1,A1),"a","b","c","d"))

 

[tuui_dm]

Hey. Thanks.
It won't work, tho.
I get a "Theres a problem in this formula" dialog box

 

[Peter_SSs]

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
	A	B
1	1	1a
2	1	1b
3	2	2a
4	2	2b
5	2	2c
6	2	2d
7	2	2e
8	2	2f
9	2	2g
10	2	2h
11	3	3a
12	3	3b
13	3	3c
14	4	4a
15	4	4b
16	5	5a
17	5	5b
18	5	5c
19	5	5d
20	6	6a
21	7	7a
Add suffix

Cell Formulas
Range	Formula
B1:B21	B1	=A1&CHAR(96+COUNTIF(A$1:A1,A1))

 

[tuui_dm]

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
	A	B
1	1	1a
2	1	1b
3	2	2a
4	2	2b
5	2	2c
6	2	2d
7	2	2e
8	2	2f
9	2	2g
10	2	2h
11	3	3a
12	3	3b
13	3	3c
14	4	4a
15	4	4b
16	5	5a
17	5	5b
18	5	5c
19	5	5d
20	6	6a
21	7	7a
Add suffix

Cell Formulas
Range	Formula
B1:B21	B1	=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

 

[Peter_SSs]

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
	A	B
1	1	1a
2	1	1b
3	2	2a
4	2	2b
5	2	2c
6	2	2d
7	2	2e
8	2	2f
9	2	2g
10	2	2h
11	2	2i
12	2	2j
13	2	2k
14	2	2l
15	2	2m
16	2	2n
17	2	2o
18	2	2p
19	2	2q
20	2	2r
21	2	2s
22	2	2t
23	2	2u
24	2	2v
25	2	2w
26	2	2x
27	2	2y
28	2	2z
29	2	2aa
30	2	2ab
31	2	2ac
32	2	2ad
33	2	2ae
34	2	2af
Add suffix (2)

Cell Formulas
Range	Formula
B1:B34	B1	=A1&LOWER(SUBSTITUTE(ADDRESS(1,COUNTIF(A$1:A1,A1),4),1,""))

 

[KRice]

Thanks for the tip on this, Peter. Much appreciated!