Pat needs to find duplicate addresses. However, the addresses are typed differently, data is in different columns, it is a real mess.
In Episode 806, we will take a look at using FuzzyMatch functions from the MrExcel Message Board to solve this problem.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
In Episode 806, we will take a look at using FuzzyMatch functions from the MrExcel Message Board to solve this problem.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Well, we have an impossibly hard question today sent in by Pat.
Pat says, I need to match addresses up.
Our customer database is just a mess.
We have addresses that enter one way and not the other way, and, you know, I hear this, but this particular one was the worst that I've seen because, for example, there’s 4 address lines and she said, hey, these are a match.
123 Mayfair Garden on New Street and Number 123 New Street on Mayfair Garden.
She wants those to match.
They're not even in the same column.
Wow.
So, I came over here.
First of all, I said, okay, let's get all of these into a single column.
So, I entered a formula of A2 & B2 & C2 & D2 so, that way, at least I have one single bit of text, and this solution goes back to a challenge of the month from long, long ago called the FuzzyMatch problem, and Juan Pablo sent in a great user-defined function that was modified and modified, and, at this point, it's come a long way.
If you just go to Google and search for FUZZY and then SITE:MREXCEL.COM, you'll get to all of these discussions.
[ =+A2&B2&C2&D2 ] This one here, Alan’s UDFs For The Fuzzy Match Problem, [ unintelligible – 01:20 ], has what I think is the definitive FuzzyMatch logic.
Click that link, and, basically, you're going to take this code that Al wrote out here, and it gives you 3 new functions.
So, we would copy all this code right from the message board and we want to paste that into Excel of course.
So, CONTROL+C to copy, come back to Excel.
Normally, what you do is Alt+F11, and then insert module, and then CONTROL+V to paste.
I've already pasted it in here.
It gives us 3 new functions, one called Fuzzy VLOOKUP, and that's the one that I'm going to start with.
Fuzzy VLOOKUP over here in column F. Basically, FUZZYVLOOKUP, I said, hey, take this value that's in E2 and go look through the rest of the addresses from E3 down to E10.
I was careful there to put a $ sign so that way it always stops at E10, and when you find it, tell me the column, column number 1, and what it does is it finds a match all the time even if there's just a couple of letters in common.
[ =FUZZYVLOOKUP(E2,E3:E$10,1) ] So, then, you have to come over to column G and use another one of Al’s functions -- FUZZYPERCENT.
That says, hey, now that we have this match, E3 and F3, tell me how well it matches?
What % of the characters go together, and so, you know, like here, these ones that match only 9% or 0% or there’s no match at all, you don't want to use those, but probably anything that has around 30% or more characters in common, those are the ones you want to go look at as possible matches.
So, this is something that’s really tough.
[ =FUZZYPERCENT(E5,F5) ] For Pat, someone had suggested some VBA and, you know, you’re supposed to go hire someone to write the VBA.
That's the great thing about the message board.
There's a lot of VBA already there, enough to at least help in solving this problem.
There's no doubt about it, it's a very tough problem trying to match up data that might be in different columns typed different ways, but using the FuzzyMatch logic, a great way to go.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, we have an impossibly hard question today sent in by Pat.
Pat says, I need to match addresses up.
Our customer database is just a mess.
We have addresses that enter one way and not the other way, and, you know, I hear this, but this particular one was the worst that I've seen because, for example, there’s 4 address lines and she said, hey, these are a match.
123 Mayfair Garden on New Street and Number 123 New Street on Mayfair Garden.
She wants those to match.
They're not even in the same column.
Wow.
So, I came over here.
First of all, I said, okay, let's get all of these into a single column.
So, I entered a formula of A2 & B2 & C2 & D2 so, that way, at least I have one single bit of text, and this solution goes back to a challenge of the month from long, long ago called the FuzzyMatch problem, and Juan Pablo sent in a great user-defined function that was modified and modified, and, at this point, it's come a long way.
If you just go to Google and search for FUZZY and then SITE:MREXCEL.COM, you'll get to all of these discussions.
[ =+A2&B2&C2&D2 ] This one here, Alan’s UDFs For The Fuzzy Match Problem, [ unintelligible – 01:20 ], has what I think is the definitive FuzzyMatch logic.
Click that link, and, basically, you're going to take this code that Al wrote out here, and it gives you 3 new functions.
So, we would copy all this code right from the message board and we want to paste that into Excel of course.
So, CONTROL+C to copy, come back to Excel.
Normally, what you do is Alt+F11, and then insert module, and then CONTROL+V to paste.
I've already pasted it in here.
It gives us 3 new functions, one called Fuzzy VLOOKUP, and that's the one that I'm going to start with.
Fuzzy VLOOKUP over here in column F. Basically, FUZZYVLOOKUP, I said, hey, take this value that's in E2 and go look through the rest of the addresses from E3 down to E10.
I was careful there to put a $ sign so that way it always stops at E10, and when you find it, tell me the column, column number 1, and what it does is it finds a match all the time even if there's just a couple of letters in common.
[ =FUZZYVLOOKUP(E2,E3:E$10,1) ] So, then, you have to come over to column G and use another one of Al’s functions -- FUZZYPERCENT.
That says, hey, now that we have this match, E3 and F3, tell me how well it matches?
What % of the characters go together, and so, you know, like here, these ones that match only 9% or 0% or there’s no match at all, you don't want to use those, but probably anything that has around 30% or more characters in common, those are the ones you want to go look at as possible matches.
So, this is something that’s really tough.
[ =FUZZYPERCENT(E5,F5) ] For Pat, someone had suggested some VBA and, you know, you’re supposed to go hire someone to write the VBA.
That's the great thing about the message board.
There's a lot of VBA already there, enough to at least help in solving this problem.
There's no doubt about it, it's a very tough problem trying to match up data that might be in different columns typed different ways, but using the FuzzyMatch logic, a great way to go.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
