Normalizing data

**jviola** · 06-01-2013, 07:41 AM

Hi all. So it turns out that our database is seriously unstandardised because of people inputting things as they feel like it. For instance, one piece of data might be "Chateau Pontet Canet" and then the next time someone puts in a record for this he or she puts "Chateau Pontet-Canet" with a hyphen. Or maybe they put in an accent the first time on an "e", and then forget it another time.

This all makes organising data a nightmare. Is there any way to get excel to recognise such similar files as being the same, or is there a way to get it to standardise these all - so change say "Chateau Pontet Canet", "Chateau Pontet-Canet" and Chateau Pontet-Canet(Bordeax)" all to "Chateau Pontet Canet" without having to go through manually and do it (I have about 15,000 files which have this problem!). At the very least, is there anyway for excel to highlight or group similar cells together - e.g. cells with the first 5 characters the same which I could then examine?

Thank you so so much to anyone who has a solution to this: I think I might die if I have to look through every single row!

**Kyle123** · 06-01-2013, 08:18 AM

Fixing it is likely to be only half of the problem, it's probably worth looking at a proper database that enforces referential integrity where you can normalise properly.

Fixing your existing data is likely to a pain, as a starter for 10 try replacing all special characters with a space, this will fix the hyphen inconsistencies. The rest will uust be a case if working out how many unique entries you have - I reckon a pivot table for each field would be your best plan of attack

**newdoverman** · 06-01-2013, 09:49 AM

This is going to be a real pain in the butt. I suggest that you make a copy of your data to work with just in case you do something that you can't recover from. I don't know how your data is organized but if each field like "Chateau Pontet-Canet" has another field that would be the same regardless of how "Chateau Pontet-Canet" is entered, like an address or ID number, you can sort on that field bringing all records with that commonality together. Then determine which is correct and make the changes. Make sure that you select the field (column) that has the data that you want to change before making changes otherwise you may be making changes in the other fields that you don't want to change.

You may find that if you copy your data to Word, you may be able to take advantage of the superior Find and Replace that Word has compared to Excel.

I've had to do this so .... my sympathies to you.

Good Luck.

**Armando Montes** · 06-02-2013, 09:48 PM

Maybe:
http://www.mrexcel.com/forum/excel-q...ighlight=fuzzy

**Sam Capricci** · 06-03-2013, 12:10 PM

I have had a similar problem taking data from an Access database into excel. My approach has been a couple step process and it works fine with my 66k plus rows. I use Filter - Advanced highlighting the column, select "Copy to another location" and check the "Unique records only" option then put the selection in an empty column. Next I do a countif function to see which "name" is used most. Then I usually do a find and replace all for the less common uses and at least my data comes out consistently. FWIW.

**Alexander Frolov** · 06-07-2013, 10:05 AM

You can try to use Fuzzy Lookup Add-in from Microsoft (not sure for the Mac version), here is a review from Debra Dalgleish
http://blog.contextures.com/archives...or-excel-2010/

If you need to perform this task frequently, the best way is to create a lookup table based on the cleaned table ("Name" -> "Clean name") and update it regularly.