Fuzzy Match in Excel 2011

purpleinnej

macrumors member
Original poster
Feb 8, 2010
62
2
Yigo, GU
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

macrumors 6502a
Dec 13, 2011
624
2
Sacramento, CA
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

macrumors member
Original poster
Feb 8, 2010
62
2
Yigo, GU
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.
 
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.