I use the Fuzzy lookup add on in Excel and i was wondering if there was a way to code what i do with it into VBA so i can just click a button rather than go through the set up each time?
I wouldn't know where to even start in coding this in VBA. I've tried recording a macro to see what populates in VBA whilst setting up the Fuzzy Match but get nothing. My original thought was surely once i have set up the match and click run it should have populated something?
In a nut shell, i have a big main table and separate smaller table that changes. I use FM To join a column between each table and pull back a maximum 10 matches with a match of over 70%. It then pulls back 6 or 7 columns from the changing data and the same 6 or 7 columns from the main data table where it thinks there may be a match.
For example, I join a column with 'titles' in. If the smaller table has a row with title 'ABC' and in the main table there are rows with 'ABC','ABC 1' etc, it will bring both those matches back with the relevant columns for say account, names etc that i had chosen it to do so.
The idea here being that everything in New Data brings back everything from All Data that has a match on the Title, so in this example there would be 2 rows for each row in the New Data table as it has found 2 potential matches for each row in the Main Data table.
Very straight forward to use with the Fuzzy Lookup add-on in excel but i really want to create a VBA button to do it automatically without the need for user input as it will be used by people unfamiliar with FM etc etc.
Any help would be massive. Surely when i click the add-on and set it up and press 'Go' it does something in the background with VBA? I just can't find it.
Also please see the set up i have used with the Fuzzy Lookup add-on here:
You can see i have told it to give me a match based on 2 columns, to bring back a maximum of 5 matches with at least an 80% match, and to bring back the selected columns, which can be seen in the attached workbook.
Due to the small amount of data i have included you will see that the add-in has found a match from every row of the main data - this obviously won't always be the case.
There's clearly meant to be an image in your last message but I can't see it.
There's a fuzzy match in Power Query which will allow %match and max number of matches but I can't see how you've set up the FM add-in.
p45cal Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Whether more by luck than design, the table at cell O22 of the attached is a Power Query fuzzy match which is exactly the same as your results table (bar the similarity rating). Note that a non fuzzy match merge also gives the same results.
You can right-click anywhere within the table and choose Refresh to update the table.
Last edited by p45cal; 09-07-2020 at 05:09 AM.
p45cal Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
However, i'm struggling to see how you managed to replicate the data i had from the Fuzzy Lookup add-in as when i go into the query editor i get the message "Expression Error: The import Table.FuzzyNestedJoin matches no exports"
Are you able to talk me through what you did to get to your table so i can replicate?
What version of Excel do you have? Is it up to date?
What version of Power Query? Is it up to date?
(Just following the advice given on that last link I gave…)
p45cal Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
I never managed to get the fuzzy nested issue sorted. However, i did manage to figure out a non fuzzy match way to (almost) get to what i want, which will be more than adequate for now!
Reply With Quote
