Fuzzy Match in Excel 2011

[purpleinnej]

I have found EXACTLY what I am looking for, but alas it is only for Windows versions of Excel (of course). Here is what I need:

I have a spreadsheet with names (split into two cells, first and last) and addresses. If the names and addresses are an approximate match, I need something to happen (highlight, delete duplicates, etc.... I can do that code myself most likely). The tricky part is addresses. I want 1234 E. Broadway Street to match 1234 East Broadway St. I cannot use left, right, or mid functions because the variable characters aren't always in the same position. The "Fuzzy Lookup For Excel Add-In" seems to be what I need, but I can't test it for sure. Is there a way to do a sort of fuzzy lookup already built into Excel? Or a macro I can copy-paste to use (I'm not familiar with them, but I know they exist and maybe it's time I learn how). Any help, suggestions, etc. are much appreciated.

 

[Pharmscott]

How about an alternative approach? Use Find and Replace on all "E." to change them to East (or vice versa). Do the same with St. vs. Street and other common abbreviations. Then you should be able to use exact match or even Filter by Unique.

 

[purpleinnej]

That's what I'm doing for now. But I would also like a better way in the future. Perhaps match names like "Steve" with "Stephen", etc. Currently I highlight all matches with the same first 4 letters of first and last name and same first 3 characters of address (usually just the street number). I do all of the conditional formatting highlighting after replacing all direction and other common address abbreviations. It's just tedious and not quite as time-saving-efficient as I would prefer.