Assigning unique id to adjacent cell in multiple columns of one worksheet

[mm5680]

I hope someone can help me with this. Example below:

Name ....... NameID ........ Location....... LocationID
Melissa ..............................Florida
John ..................................New Jersey
Mary ................................ New Jersey
Robyn ...............................California
John ..................................Florida

My goal is to have the NameID column and Location ID column assign a unique id for each name and each location keeping the same names with the same Id and same locations with the same id.


I have tried =IF(COUNTIF($F$2:F2,F2)=1,MAX(LocationID:LocationID,0)+1,INDEX(LocationID:LocationID,MATCH(F2,Location:Location,0),1))

but it regenerates new ID's whenever I save the document or open it again. Also, it creates Id's in the hundreds and keeps going up, now I have Id's that are in the 7000's. I need this to work in such a way that if I change Melissa's location to New Jersey it will know to assign her the New Jersey ID.

Thanks,
Missy

 

[taigovinda]

Is there a static number of possible locations? Is it just the 50 states? Make a table listing all the states numbered 1 through 50, then have your locationid column be a v-lookup off of that table.

Am I missing something in your question? ...Are you adding more and more data, like you have two rows in your sample for John? Do you want entry of Florida for the second John to change New Jersey to Florida for the first John? I'm really not clear on what you are trying to accomplish.

 

[mm5680]

thank you for replying...i apologize for not being clear....

The Locations and Names will change depending on the list...I was showing that there are 2 johns in the list and if in the future I happen to change one of their locations i would like excel to change the location id to the corresponding id

 

[taigovinda]

Sorry, I still don't understand. Can you give an explicit example of what you already have, what you enter, and what you want to change when you make the entry?

 

[mm5680]

Current List:

Name ....... NameID ........ Location....... LocationID
Melissa ..............................Florida.......................
John ..................................New Jersey.......................
Mary ................................ New Jersey.......................
Robyn ...............................California.......................
John ..................................Florida.......................

Desired List after entering formula:

Name ....... NameID ........ Location........... LocationID
Melissa ..........100...............Florida................200
John ..............101...............New Jersey........201
Mary ..............102..............New Jersey.........201
Robyn ............103..............California.............202
John ...............101..............Florida................200

Then in the future if I change for example: Melissa's location to California, her locationID should change to 202

 

[taigovinda]

You would enter a new line for Melissa and her LocationID would *change* to 202 in the earlier entry?

Or you just want to type in only the name and location, and have the NameID and LocationID pop up automatically?

If either of these is what you want to do, I don't see how John in your example has one NameID but has two different locations...

Or maybe are your locations and names actually completely unrelated? So for purposes of this exercise, the people reading your question could answer only for NameID and you could use the same formula for both columns? You just want to assign a unique I.D. to each new entry in a column?

 

[mm5680]

You would enter a new line for Melissa and her LocationID would *change* to 202 in the earlier entry?

Or you just want to type in only the name and location, and have the NameID and LocationID pop up automatically?

If either of these is what you want to do, I don't see how John in your example has one NameID but has two different locations...

Or maybe are your locations and names actually completely unrelated? So for purposes of this exercise, the people reading your question could answer only for NameID and you could use the same formula for both columns? You just want to assign a unique I.D. to each new entry in a column?

in the example their are two different 'johns' one is in New Jersey and the other one is in Florida. I would like a formula that would generate a unique id for each name and an id for each location.