Back in episode 2297, Evan asked how to VLOOKUP between two lists where one had DOE, JANE and the other had Jane Doe. This video solves it with Power Query's new Fuzzy Match. Thanks to @XLarium @Daniel Dion and @Bart Titulaer for their suggestions that made this solution twice as quick as before.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2301.
Fuzzy Match for Jane Doe matching to Doe, Jane.
Hey, welcome back to the MrExcel Netcast.
I'm Bill Jelen. We're going to revisit my solution to episode 2297.
I love putting videos on YouTube because the comments that come along XLarium am Daniel Dion, both came along and said What about a fuzzy match, and sure enough, Wow! I tried it and it worked.
Also, going to use Bart's comment from another video that I could save a couple of clicks.
Alright, so revisiting the problem.
This was Evan who I ran into one of my seminars has data like this with first name, comma, last name, in proper case and he needs to match that to this other list that is last name, comma, first name.
Now we're going to use Power Query to solve this.
Both of these have to be formatted as table.
I've renamed the tables this one to be called RightTable.
This would be called LeftTable right?
And we're going to start with the look up table. The second table over here.
On the Data tab select From Table or Range.
And that's it. That's all we have to do. Choose Close and Load..., Close and Load To...
Only Create a Connection.
Now back in 2297 there were a whole bunch of gyrations I did there to split it at the comma, rearrange the data, convert to proper case.
All that goes away. Then over here Data, From Table or Range.
Right, and then once we have this query, we're going to come up here and Merge this query.
This is Bart's idea.
The merge says we take the left table and I'm going to merge it with the connection that we created for the right table.
This is the field name and this is the field name and then right here this box is new box.
Use Fuzzy Matching To Perform The Merge.
Now on the podcast before we talked about the fuzzy match at in that tool from Microsoft Labs, this is now kind of built-in.
That functionality is built and been built into Power Query and we get the exact same results that we had before the selection matches 104 of 109 rows from the first table. So we choose OK.
And then expand the table here.
And Adam Weaver is now matched up to Weaver comma Adam.
Exact same four items that were missing from the other episode.
Ann Bowman. There was no Bowman comma Ann right?
So you just get nulls there. Finally close and load.
And we have our update like that, right? So it's beautiful.
It works. It's much much faster.
So again my shout-out to XLarium, Daniel, and Bart - really appreciate those comments.
And if you have comments, questions, anything please feel free to leave it in below the YouTube video.
Check out my new book MrExcel LX, the Holy Grail Excel tips filled with all my favorite Excel tips.
And if you like what you see here, please subscribe and ring that Bell.
I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
Fuzzy Match for Jane Doe matching to Doe, Jane.
Hey, welcome back to the MrExcel Netcast.
I'm Bill Jelen. We're going to revisit my solution to episode 2297.
I love putting videos on YouTube because the comments that come along XLarium am Daniel Dion, both came along and said What about a fuzzy match, and sure enough, Wow! I tried it and it worked.
Also, going to use Bart's comment from another video that I could save a couple of clicks.
Alright, so revisiting the problem.
This was Evan who I ran into one of my seminars has data like this with first name, comma, last name, in proper case and he needs to match that to this other list that is last name, comma, first name.
Now we're going to use Power Query to solve this.
Both of these have to be formatted as table.
I've renamed the tables this one to be called RightTable.
This would be called LeftTable right?
And we're going to start with the look up table. The second table over here.
On the Data tab select From Table or Range.
And that's it. That's all we have to do. Choose Close and Load..., Close and Load To...
Only Create a Connection.
Now back in 2297 there were a whole bunch of gyrations I did there to split it at the comma, rearrange the data, convert to proper case.
All that goes away. Then over here Data, From Table or Range.
Right, and then once we have this query, we're going to come up here and Merge this query.
This is Bart's idea.
The merge says we take the left table and I'm going to merge it with the connection that we created for the right table.
This is the field name and this is the field name and then right here this box is new box.
Use Fuzzy Matching To Perform The Merge.
Now on the podcast before we talked about the fuzzy match at in that tool from Microsoft Labs, this is now kind of built-in.
That functionality is built and been built into Power Query and we get the exact same results that we had before the selection matches 104 of 109 rows from the first table. So we choose OK.
And then expand the table here.
And Adam Weaver is now matched up to Weaver comma Adam.
Exact same four items that were missing from the other episode.
Ann Bowman. There was no Bowman comma Ann right?
So you just get nulls there. Finally close and load.
And we have our update like that, right? So it's beautiful.
It works. It's much much faster.
So again my shout-out to XLarium, Daniel, and Bart - really appreciate those comments.
And if you have comments, questions, anything please feel free to leave it in below the YouTube video.
Check out my new book MrExcel LX, the Holy Grail Excel tips filled with all my favorite Excel tips.
And if you like what you see here, please subscribe and ring that Bell.
I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
