Excel Duplicate cell change

[jahgardener]

I have a legacy database that I need to migrate to another program. The database contains company names and addresses. The new program requires the company name be unique. This was not the case with the legacy database.
I have the data in an excel spreadsheet, and I can find the duplicates. What I would like to do is to change each duplicate name to something unique globally. I have to keep the record as other items are tied to it's key field throughout the entire system, so the company name while important, is secondary to the key field that I will also be entering.

For Example,

The Mayo Clinic 12345
The Mayo Clinic 36780
The Mayo Clinic 98763

I was thinking something along the lines of

The Mayo Clinic 12345
The Mayo Clinic* 36780
The Mayo Clinic** 98763

The first entry being unique, and then any other having an additional asterisk so the new database will accept each company as unique. I had considered just adding the city to the company name as a way to differentiate, but these clients are somewhat sloppy in the data entry dept. so there is no guarantee that that field would be available.

The spreadsheet is 25000 in record length and there are well over 1000 duplicates for hundreds of companies.

Any help would be greatly appreciated. I could probably fuss with this a bit, and get something to work, but thought someone out there knows an elegant solution to this dilemma.

Many thanks, ja

 

[SkipVought]

Hi,

I would not use asterisks. I'd append a number. A simple formula would work, like...
[tt]
=if(A2=A1,c1+1,1)
[/tt]
assuming that the name is in column A starting in row 2 and the formula is in column C.

Append the values in column A to column C.

Skip,

_[red][/red]