Lots of text data, need to assign unique numeric values

Hi,
Here's my situation.

I will post a rudimentary example:
Account Name/Column A
Truman school, San Diego
Truman school, San Diego
Truman school, San francisco
Truman school, Atlanta
Truman school, Atlanta


In column B, I would like it to assign a unique numeric id based on the TEXT only.

So Truman school, San Diego gets a string of numbers: 1234
Truman school, san fran: 1235
Truman school, Atlanta: 1236

My spreadsheet is about 6,000 records with a great deal of the above situation going on. So school names are identical with different locations assigned. I am trying to import to a database and don't want a lot of duplicate records. That's why I'm trying to assign a numeric value.

Any help would be appreciated.

Thank you!

At the top of column B enter the number before your first desired code...

e.g. in B1 enter 1233

Then in B2 enter formula:

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

and copy down..

You can then copy/paste special >> Values over the formulas... so that they become hard coded.. and then delete B1.

If you want codes repeated for duplicate...then use formula in B2:

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

Note: Both these formulas assume data is grouped by as per your example.

You are welcome.

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save