I'm trying to create an alpha numeric code for a massive list of direct mail recipients compiled in excel. The code would be [first letter of last name]&"_"&[number 1-10,000 based on alpha order within specific letter (see below)]. Examples below. I can figure out the first letter with left but can't figure out how to restart the count based on the switch from A to B, B to C, etc. This can be done manually obviously but I'd prefer to not have to do that each time I update the list it! Please help!
<colgroup><col><col><col></colgroup><tbody>
</tbody>
Code | First Name | Last Name |
A_1 | John | Adams |
A_2 | John Quincy | Adams |
A_3 | Chester | Arthur |
B_1 | James | Buchanan |
C_1 | Jimmy | Carter |
C_2 | Grover | Cleveland |
C_3 | Grover | Cleveland |
C_4 | Bill | Clinton |
C_5 | Calvin | Coolidge |
E_1 | Dwight | Eisenhower |
F_1 | Millard | Fillmore |
F_2 | Gerald | Ford |
<colgroup><col><col><col></colgroup><tbody>
</tbody>