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!


[TABLE="width: 230"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Code[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[/TR]
[TR]
[TD]A_1[/TD]
[TD]John[/TD]
[TD]Adams[/TD]
[/TR]
[TR]
[TD]A_2[/TD]
[TD]John Quincy[/TD]
[TD]Adams[/TD]
[/TR]
[TR]
[TD]A_3[/TD]
[TD]Chester[/TD]
[TD]Arthur[/TD]
[/TR]
[TR]
[TD]B_1[/TD]
[TD]James[/TD]
[TD]Buchanan[/TD]
[/TR]
[TR]
[TD]C_1[/TD]
[TD]Jimmy[/TD]
[TD]Carter[/TD]
[/TR]
[TR]
[TD]C_2[/TD]
[TD]Grover[/TD]
[TD]Cleveland[/TD]
[/TR]
[TR]
[TD]C_3[/TD]
[TD]Grover[/TD]
[TD]Cleveland[/TD]
[/TR]
[TR]
[TD]C_4[/TD]
[TD]Bill[/TD]
[TD]Clinton[/TD]
[/TR]
[TR]
[TD]C_5[/TD]
[TD]Calvin[/TD]
[TD]Coolidge[/TD]
[/TR]
[TR]
[TD]E_1[/TD]
[TD]Dwight[/TD]
[TD]Eisenhower[/TD]
[/TR]
[TR]
[TD]F_1[/TD]
[TD]Millard[/TD]
[TD]Fillmore[/TD]
[/TR]
[TR]
[TD]F_2[/TD]
[TD]Gerald[/TD]
[TD]Ford[/TD]
[/TR]
</tbody>[/TABLE]

 

[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