Need VLOOKUP to track if I have multiple entries for the same site number

In my example table below I have a workbook that contains an "Index Key" column that shows if I have multiple duplicate site # entries by designating an A for 1, B for the 2nd instance, C for the 3rd and so on. I am doing a VLOOKUP against a vendor supplied workbook daily and my question is how do I track if the vendor add more entries for the same site #? (i.e. if I show 4 entries for site 49027 below and tomorrow's workbook I receive has 6 entries for the same site #. I created the Index Key manually and now I need to automate the process. I appreciate your assistance.


Index Key Site # Scope
A 49027 WAN Circ Only
B 49027 WAN Circ Only
C 49027 WAN + Rtr Mgmt
D 49027 WAN Circ Only
A 49028 WAN Circ Only
B 49028 WAN + Rtr Mgmt
A 53920 WAN + Rtr Mgmt
A 58836 WAN + Rtr Mgmt

Not sure if you have flexibility in column usage, but, the easiest way to do this is as follows:
~ In column A, leave the site # as entered by vendor.
~ In cell B2, enter the following formula and then copy it to the bottom of your data (note that the top of the count range is absolute whereas the bottom of the range is relative):
=COUNTIF(A$2:A2,A2)+64
~ Down column C, copy this formula:
=CHAR(B2)&" "&A2


Column B counts the number of instances you have of a site number and converts it to a code associated with characters A, B, C, etc
Column C converts this code to the actual letter and combines it with the site number to provide your final Index Key.
Afterwards, you would get the following output if you were to add another site 49027 to the bottom of the list:
Site # Index Code Index Key
49027 65 A 49027
49027 66 B 49027
49027 67 C 49027
49027 68 D 49027
49028 65 A 49028
49028 66 B 49028
53920 65 A 53920
58836 65 A 58836
49027 69 E 49027

Thank you all for your excellent support. It works. I'm not sure how the A, B, C, etc. code works. How does 65 = a, 66= b and so on? I glad I found a forum for support. I have some more that I will throw your way if you have time. Once again thanks for the quick responses.

I gusess my boss was looking for something different. I should have explained why I would need the index key in a spreadsheet. Your formulas are great but, my problem is that only my master spreadsheet will have the index key and customers will be sending me spreadsheets with only one site number but multiple different circuit information. So, for example, I may have site 45128 four times with different circuit information on each row and, my customer will send me a spreadsheet with one row for site 45128 and a circuit number. I need to know which circuit number cooresponds to my row(s). I cannot ask the customer to insert an index key in their sheets. I look forward to your feedback. Thx

It sounds like you need to input the circuit number into your master sheet and use it (as opposed to random letters) in conjuction with the site # to create your unique identifier (Index Key). I do think we'll need specific examples from each of your source docs to help much further.

Thanks again for everyones help. I have attached two sheets (one is my file and the other is the customers). It looks like we got the customer to agree to use an index key unfortunatley, they placed the key behind the site number and mine is in front. The customer will be sending me a daily update and I will need to transfer alot of there information into my sheet. I guess I can have vlookups for the cells i need to capture. thx again