Formula to create custom Autonumber

[neo_gold]

Hello All,
I need some help in creating a custom autonumber in excel. The autonumber will be based on the value of another cell's value. So for eg, in cell A1 will look at B1, if B1 has the number 1, A1 will take B1 and add the autonumber to it, eg a, b, c.
If within, b5 the number is 2. So if the number in the corresponding row in b changes, the autonumber must restart.

Ive decided to go with a-z for the autonumber as i realized for .1, .2, .3 is limited to 9 values in the list bec. 1.10 may be read as 1.1
I've tried a few options such as creating a named range, however the problem is getting the autonumber to restart. Below is what i want to achieve via excel formula (not vba please)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column F[/TD]
[TD]Column F[/TD]
[/TR]
[TR]
[TD]1.a[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1.b[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2.a[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2.b[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3.c[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3.a[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance!

 

[Aladin Akyurek]

[TABLE="width: 133"]
<COLGROUP><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4039" width=114><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl63, width: 114, bgcolor: transparent"]AutoNumber[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Value[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]1.a[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]1.b[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]2.a[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]2.b[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]2.c[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]3.a[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]3[/TD]
[/TR]
</TBODY>[/TABLE]

=B2&"."&CHAR(96+COUNTIF($B$2:B2,B2))

 

[neo_gold]

wow thanks Aladin!
One thing, when I tested the last value has the value of "`" so i changed the value from B2 to B1 throughout the formula and its good now (for the first cell A1). I assume u just happened to copy the formula from cell B instead of A.
Just for my knowledge and others, the formula is saying: take the value of B1 and count within that column range the number of times B1 exists. For each time it exists, add one to the character count. The character count starts at a, so that gives it the autonumbering. If its the first time its found in the range, it resets counting from A...

 

[Aladin Akyurek]

wow thanks Aladin!

You are welcome.

One thing, when I tested the last value has the value of "`" so i changed the value from B2 to B1 throughout the formula and its good now (for the first cell A1). I assume u just happened to copy the formula from cell B instead of A.
Just for my knowledge and others, the formula is saying: take the value of B1 and count within that column range the number of times B1 exists. For each time it exists, add one to the character count. The character count starts at a, so that gives it the autonumbering. If its the first time its found in the range, it resets counting from A...

If you don't have headers and autonumbering takes place in column A depending on the values in column B, we would have:

=B1&"."&CHAR(96+COUNTIF($B$1:B1,B1))

This says: Concatenate B1 with the char obtained from B1's current occurrence frequency (done with a progressive countif) which is added to 96 for 96 + 1 --> 97 --> a; 96 + 2 --> 98 --> b; and so on. When B1 changes to a diffrent value, the concatenated result changes accordingly.

 

[neo_gold]

excellent, thanks again!

 

[Aladin Akyurek]

excellent, thanks again!

You are welcome.