Excel - Mastering Address Matching in Excel with FuzzyMatch Logic | Excel Tutorial - Episode 806
Microsoft Excel Tutorial: Mastering Address Matching in Excel with FuzzyMatch Logic | Excel Tutorial.
Welcome back to the MrExcel netcast! In today's episode, we have a challenging question sent in by Pat. Pat needs to match up addresses in a messy customer database. The addresses are entered in different ways and Pat wants to find matches even if they are not in the same column. This is definitely a tough problem, but we have a solution using the FuzzyMatch logic.
To start, we need to get all the addresses into a single column. I used a formula to combine the address lines into one cell. Then, I turned to a user-defined function called FuzzyMatch, which was originally shared by Juan Pablo and has been modified over time. You can find all the discussions about FuzzyMatch by searching "FUZZY SITE:MREXCEL.COM" on Google. The definitive FuzzyMatch logic can be found in Alan's UDFs for the Fuzzy Match Problem.
To use this logic, we need to copy the code from the message board and paste it into a module in Excel. Then, we can use the three new functions that are created: Fuzzy VLOOKUP, FuzzyPercent, and FuzzyMatch. In our example, we use Fuzzy VLOOKUP to find a match for the address in cell E2 within the range of addresses in E3 to E10. Then, we use FuzzyPercent to determine how well the match is, based on the percentage of characters that are the same.
This method may not give a perfect match every time, but it can help narrow down the possible matches. For Pat's problem, someone had suggested using VBA, but with the help of the message board, we were able to find a solution without having to hire someone to write VBA code. The FuzzyMatch logic is a great tool for matching up data that may be entered in different ways. It may be a tough problem, but with this method, we can find some possible matches and clean up the messy customer database.
Thank you for tuning in to this episode of the MrExcel netcast. We hope this solution helps you with any similar problems you may encounter. Don't forget to check out our other netcasts for more Excel tips and tricks. See you next time!
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
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.
Table of Contents
(00:00) Matching Addresses that are close matches
(00:50) Join all fields into a single column
(01:22) Fuzzy Match Macro
(02:08) Using Macro for FUZZY VLOOKUP in Excel
(03:02) Measure quality of match with FUZZY PERCENT
(03:16) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorialalightmotion
This video answers these common search terms:
Address formatting
Customer database
Data cleaning
Data matching
Excel user-defined functions
Fuzzy VLOOKUP
FuzzyMatch problem
FuzzyPERCENT
Matching addresses
VBA (Visual Basic for Applications)
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...