Matching misspelled or differently spelled names

[dsciola]

I have 2 lists of company names, list A and list B. I want to find all the names in A that are also in B. Problem is, some of the names in A are spelled differently than in B, but are the same entity. For example, List A has 'Amazon', and List B has 'Amazon.com.' How do I tell Excel to find a misspelled match, so to speak, like this? I originally tried using 'COUNTIF' and then counting the ones that match, but the spelling differences make it not possible that way

Please see attached spreadsheet for example and feel free to ask any clarifying questions. Note that these lists are relatively small, the real stuff I'm working in has thousands of names that I need to do this to...hence manually checking each name would be a colossal pain.

Dom

[cffndncr]

If it's just additions before and after the name, it's not too hard. However, do you want it to match 'Amazon' and 'Aamzon'? That is considerably more difficult.

Edit: I should have looked at your example sheet first, nevermind!

[Richard Buttrey]

Hi,

With

=MATCH("*"&LEFT(C3,5)&"*",$E$1:$E$22,FALSE)

in B2 copied down this will return the row number in list B that contains the first 5 characters of the names in List A. That's the LEFT(C3,5) bit.
You can of course vary this constant 5 to increase or decrease the sensitivity to mis-spelling.

However note that it can't pick up the 3-D 'mis-spelling' of 3D in B4. The other N/A values in B5 & B6 are where the names don't exist.

You really need a fuzzy logic tool to help you detect mis-spellings. Microsoft have an Excel Add In here http://www.microsoft.com/en-us/downl....aspx?id=15011 which I have found useful on several occasions.

[dsciola]

Hi,

You really need a fuzzy logic tool to help you detect mis-spellings. Microsoft have an Excel Add In here http://www.microsoft.com/en-us/downl....aspx?id=15011 which I have found useful on several occasions.

OK. I tried downloading that and using it just on this table. It doesn't seem to be working. Is there a video on how to use it or something? Either I'm having trouble figuring it out or the add-in is bugging out. I think something like this would definitely help if it worked. I definitely need something that could pick up that 3-D 'mis-spelling' for the main, and bigger, tables I'll be workin' with. They have thousands of names that have potentially similar mis-spellings.

Thanks!
Dom