Adding Sequential Numbering to a Non-Sequential List

[Astayonix]

I'm a bit stumped by this one. I'm trying to make a formula that will add sequential numbering to a non-sequential list. Here is an example of what I'm talking about.

Category	Category with Sequential Numbering	Match Result
D	D01	4
A	A01	5
A	A02	5
C	C01	7
B	B01	8
B	B02	8
B	B03	8
A	A03	5
A	A04	5
C	C02	7
C	C03	7
C	C04	7
C	C05	7
A	A05	5

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I've tried using the match function, and it groups the "like" categories (A's, B's, C's, and D's), but I'm unsure how to add the incremental numbering. I also need the first instance of every new category to start with 01 (A01, B01, C01, and D01) before the formula walks down the list and adds the next numerical increment to the next instance of that category's appearance. Any help and/or ideas rendered would be GREATLY appreciated!

-=Astayonix=-

 

[Weazel]

Just a thought assuming the letters are in column A

=A2&TEXT(COUNTIF($A$2:A2,A2),"00")

 

[Astayonix]

Just a thought assuming the letters are in column A

=A2&TEXT(COUNTIF($A$2:A2,A2),"00")

Hi, Weazel!

Thank you for the response! I tried the COUNTIF, and while it was able to count the number of instances of each of the categories (per below), it doesn't anchor the first instance of each (A01, B01, C01, and D01), and it doesn't add the incremental numbering to each sequence (A01, A02, A03, A04, A05, etc.). The MATCH function is able to properly segregate the categories (A's, B's, C's, and D's), and the COUNT-IF returns the number of times that each category occurs in the list, but the sequential numbering is probably some type of hybrid use of both (??). This one is kind of sticky! Thank you very much for the input!

Category	Category with Sequential Numbering	Match Result	Count-If Result
D	D01	4	D01
A	A01	5	A05
A	A02	5	A05
C	C01	7	C05
B	B01	8	B03
B	B02	8	B03
B	B03	8	B03
A	A03	5	A05
A	A04	5	A05
C	C02	7	C05
C	C03	7	C05
C	C04	7	C05
C	C05	7	C05
A	A05	5	A05

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

-=Astayonix =-

 

[Rick Rothstein]

Hi, Weazel!

Thank you for the response! I tried the COUNTIF, and while it was able to count the number of instances of each of the categories (per below), it doesn't anchor the first instance of each (A01, B01, C01, and D01), and it doesn't add the incremental numbering to each sequence (A01, A02, A03, A04, A05, etc.).

Did you use the EXACT formula Weazel posted or did you attempt to modify it? His formula, exactly as posted, does exactly what you are describing you want for me when I test it against the data you posted.

 

[Astayonix]

Did you use the EXACT formula Weazel posted or did you attempt to modify it? His formula, exactly as posted, does exactly what you are describing you want for me when I test it against the data you posted.

Hi, Rick!

You are correct! I had modified it a bit to include a specific range on the COUNTIF! It DOES work properly! It both anchors the 01 entry for each category and it sequentially numbers each addition to the category! This is AMAZING! Thank you both so much! I truly appreciate it!

-= Astayonix =-