Creating Alpha Numeric Code by Last Name

[jg10009]

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!

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>

 

[offthelip]

try this:
=IF(LEFT(C3,1)=LEFT(C2,1),LEFT(C3,1) & "_" & MID(A2,3,255)+1,LEFT(C3,1) & "_1")

I forgot to say you need to put A_1 into the cell A2

 

[Rick Rothstein]

Assuming you have header text in Row 1 and that your data starts on Row 2, put this formula in cell A2 and copy it down...

=LEFT(C2)&"_"&COUNTIF(C$1:C1,LEFT(C2)&"*")+1

 

[mole999]

dosen't that code have to be a permanent fixture? others wise your next references will be out