I have a problem on Excel 2010.
I have a column which lists the names of manufacturers', like so:
Manufacturer
Company 1 Ltd
Company 2 Ltd
Company 3 Ltd
My problem is when there's a spelling mistake in the manufacturer's name or it's missing the ltd or adds a "Co" between the company name and ltd.
Is there a way that I can flag similar text and then somehow replace one with the other?
I thought about conditional formatting and fuzzy lookup but the cells are similar and not duplicates and fuzzy lookup requires 2 columns.
Thanks Everyone!
51 Answer
This can be done in 2 ways.
By using a special add-in, free for the first 15 days: our Fuzzy Duplicate Finder is specially designed for this task – find similar (fuzzy) matches and correct typos and misprints.
By using formulas – free but time-consuming way:
2.1. Copy the source column to an empty sheet, and name it "Original Name".
2.2. Remove duplicates.
2.3 Copy the duplicate-free column and paste it nearby, name it "Correct Name".
2.4 Sort the "Correct Name" column alphabetically from A to Z.
2.5 Find all typos in the "Correct Name" column (manually or using Fuzzy Duplicate Finder) and correct them. It will be your Lookup table.
2.6 On the original sheet, create a helper column, and then use the Index/Match formula to search in the Original Name column, and return values from the Correct Name column. If a value is not found, return something like "Not found" or "To be added to db". For example:
=IFERROR(INDEX(Table2[#All],MATCH(A2,Table2[[#All],[Original Name]],0),2),"Not Found")
For more information, please see INDEX & MATCH functions in Excel - a better alternative to VLOOKUP
