Assign a number to on the left row to unique entries on a list

Hi,

I have a list of names that has some repeated values, I would like to be able to put a number that identifies each unique name and repeats for each unique entry.

It is the same concept as in

https://www.excelforum.com/excel-for...in-a-list.html

where they use the formula:

=IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,VLOOKUP(A2,A$1:B1,2,0))

in this case this puts it in the column to the right but I need it in the column to the left and after playing with the formula for 2 hours I cant figure it out.

Here is an example of my list:

Excel Forum Question.png

I would like the numbering on the left. If it matters this list will be about 5000 names long, and I will copy them into the list.

If you guys and gals can help me up to here I will be very appreciative.


As a plus, if we can include a two letter identifier to the numbering like for KC0001 it will be also great to see how this formula will look just in case I need to do this in the future.

THANKS FOR ALL YOUR HELP!

Oscar

Hi all- This approach generates codes instead of numbers. Paste this in D3 and copy down:

Please Login or Register  to view this content.

Row\Col	A	B	C
2	Code	First Name	Initial
3	KC0001	Trey	W
4	KC0002	Fran	E
5	KC0003	Herb	P
6	KC0004	Jon	K
7	KC0005	Will	O
8	KC0004	Jon	K
9	KC0002	Fran	E
10	KC0001	Trey	W
11	KC0006	Bonny	T
12	KC0001	Trey	W
13	KC0003	Herb	P
14	KC0001	Trey	W
15	KC0001	Trey	W
16	KC0005	Will	O

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee