Need help, how to increment numbers for each unique text string.

[tHeRoBeRtMiTcHeLL]

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

 

[Tom Ogilvy]

insert a new column A and B
Assume you data starts in the second row
in B2 put in the formula =C2&E2 (city & state)
in A1 put in a zero
in A2 put in the formula
=if(b1<>B2,A1+1,A1)
then select A2:B2 and double click the little square on the lower right side
of the highlight (or drag fill down the column).

Now, select column A and do Edit=>Copy and then immediately Edit=>Paste
Special and Select Values (to replace the formulas)

Now you can delete column B.

--
Regards,
Tom Ogilvy

 

[Guest]

If you are trying to fill column A with CITY_IDs, first move the other
columns over to leave space in column A. In A1 put 1

In A2 something like
=IF(B2&D2 = B1&D1,A1,A1+1) and copy down

I have not tested this one.

 

[tHeRoBeRtMiTcHeLL]

Tom,

Your solution worked like a charm, and is a great workaround to using
code.
It's also re-usable.. in concept and application. I guess I never
thought of "B1<>B2"
as being a way to validate the uniqueness of a combined text string
from two cells... WOW!
Now I can move on, and try to remember this one in the future. Thanks
for turning my PITA around.

Robert