Hello everyone,

I've run into a wall. I have a 4 column list of data.
Column 1 has a list of US Cities.
Column 2 has a list of Unique IDs for each State in Column 3.
Column 3 has a list of US States.
Column 4 has a list of US Zip Codes for the above entities.

Ex.

CITY STATE_ID STATE ZIPCODE
Akiachak 1 Alaska 99551
Akiak 1 Alaska 99552
Akutan 1 Alaska 99553
Abbeville 2 Alabama 36310
Adamsville 2 Alabama 35005
Addison 2 Alabama 35540

(Etc. With a total of 28,758 rows of the same.)

The following is an example of what I need done.. and I have decided
that manually doing this would be a rather mundane PITA.

CITY_ID CITY STATE_ID STATE ZIPCODE
1 Akiachak 1 Alaska 99551
2 Akiak 1 Alaska 99552
3 Akutan 1 Alaska 99553
4 Anchorage 1 Alaska 99508
4 Anchorage 1 Alaska 99515
4 Anchorage 1 Alaska 99518
5 Abbeville 2 Alabama 36310
6 Adamsville 2 Alabama 35005
7 Addison 2 Alabama 35540
8 Anniston 2 Alabama 36205
8 Anniston 2 Alabama 36206
8 Anniston 2 Alabama 36201

My question is this, is there an easier way to do this...
programmatically or via formula? If so, can someone please
give me a hint in the right direction?

I'm guessing it would need to begin with Row 2 and first lookup Column
3 (StateID) then begin to read Column 2 (City - text string)If it is
found to be UNIQUE when comparing to the previous row's text string AND
(very important - StateID remains the same value) THEN Column 1
(CityID) should be assigned a value beginning with 1.
If it is found to be a duplicate string then it should retain the
(CityID) value of the previous row. It then repeats until it finds no
data (NULL). I'm guessing that if this is done with code it will
contain a FOR, IF, NEXT statement and maybe a DO WHILE or two.

Thanks,
Robert