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")) |
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!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))
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,"")) |