Strange Formatting Issue

**Pfafkl13** · 08-23-2010, 10:15 AM

Dear Forum,

I have a sheet that contains data from two different marketing databases. I would like to combine them and sort out the duplicates but a lot of the companies are not formatted the same way.

Ex.

A A Holland Company
A.A. Holland Company
AA Holland Co.

These are all the same company but they are formatted differently because the source is different. Is there an easy way to have Excel recognize that those three examples are all the same company?

Thanks for your help.

**Pfafkl13** · 08-23-2010, 02:55 PM

Bump for some help please.

**shg** · 08-23-2010, 02:59 PM

The issue has nothing to do with formatting, the text is different.

Suggest you sort by name and replace variants with whatever is the correct name.

**darkyam** · 08-23-2010, 03:02 PM

Do we assume that you have to keep updating this list and recombining the spreadsheets? If so, a vlookup table, though it will take some time to build based on how many entries you have, is likely your best bet for a long-term solution.

If this is one-time, then Ctrl+H, Find *Holland* and replace with AA Holland Co. or however you want it to appear. Please note that the first search term has to be unique to that company, yet be a common thread in all variants of the name.

**Pfafkl13** · 08-23-2010, 03:05 PM

It will be a one time thing.

The problem is that there are 17,500 rows and I would prefer not to search through all of them manually if possible. Am I SOL since the text is different?

**darkyam** · 08-23-2010, 03:19 PM

Pretty much. There's no way for Excel to know which names are the same and which are different. ABC Co. could have a parent company of XYZ Co. that you want to merge it with or two different companies could have very similar names. Sorry. Shg's option is probably the best one for a one-time use.