Matching strings that are slightly different

[M0X3]

I have two columns with names. The columns have some names that match and others that don't, but the ones that do match aren't always spelled the same. One column might have "Apple Company" the other might have "The Apple." The two names could also match perfectly or have any sort of differences. How would I match these two columns automatically and format them so that they come together on one row? So far I've done it for the case where they match the first few characters, but that leaves out about 10% of matches that are slightly different.

 

[Joe4]

Welcome to the Board!

This is a very tricky and difficult problem that comes up quite often. The issue is that what does and does not constitute a match canbe very hard to define, and inevitably always has "exceptions". You could have records that appear to be similar at first glance, but are actually two different entities.

Remember, Excel is a very literal program and just does what you tell it. That means before it can do anything, you have to tell it precisely what the rules are, which means you need to define those rules, clearly and precisely, upfront. Just trying to come up with those rules can be a daunting task, in and of itself.

There are some threads on some "Fuzzy Match" logic that may help you. See this link here: Alan's UDFs for the Fuzzy Match problem

Quite frankly, if you have about 90% success with what you are doing now, I would say that is very good. You are going to be very hard-pressed to come up with any solution that handles 100% of the cases correctly, unless your data size is small and/or unchanging, so you are able to define all the rules (and not have to worry about new exceptions coming along later on down the road).

 

[M0X3]

Welcome to the Board!

This is a very tricky and difficult problem that comes up quite often. The issue is that what does and does not constitute a match canbe very hard to define, and inevitably always has "exceptions". You could have records that appear to be similar at first glance, but are actually two different entities.

Remember, Excel is a very literal program and just does what you tell it. That means before it can do anything, you have to tell it precisely what the rules are, which means you need to define those rules, clearly and precisely, upfront. Just trying to come up with those rules can be a daunting task, in and of itself.

There are some threads on some "Fuzzy Match" logic that may help you. See this link here: Alan's UDFs for the Fuzzy Match problem

Quite frankly, if you have about 90% success with what you are doing now, I would say that is very good. You are going to be very hard-pressed to come up with any solution that handles 100% of the cases correctly, unless your data size is small and/or unchanging, so you are able to define all the rules (and not have to worry about new exceptions coming along later on down the road).

Thanks for the feedback! I would just take the 90% success rate but I've been specifically asked to improve it, so it's not up to me.
On that note, I just checked out FuzzyLookup and it looks amazing. Do you know how I would implement that? It seems like I would have to make a macro out of it using VBA.

 

[Joe4]

Yes, they use VBA, specifically creating User Defined Functions (which are functions that you create in VBA, but can use like most any other native Excel functions).
Those links should show you how to set them up and use them. There isn't really much that I can add to it that isn't already there, especially since we do not have any of the details of your data structure and/or rules.

 

[daregu]

Thanks for the feedback! I would just take the 90% success rate but I've been specifically asked to improve it, so it's not up to me.
On that note, I just checked out FuzzyLookup and it looks amazing. Do you know how I would implement that? It seems like I would have to make a macro out of it using VBA.

If you use power query you can do fuzzy matching and even add a helper table that would be able to get you that. You don't need VBA at all.

 

[M0X3]

If you use power query you can do fuzzy matching and even add a helper table that would be able to get you that. You don't need VBA at all.

Are you saying power query has that functionality already included or are you saying I can add it to power query somehow?

 

[daregu]

Are you saying power query has that functionality already included or are you saying I can add it to power query somehow?

Correct, if you load both sets of data into power query and then merge them, you can click the box for fuzzy matching. then under advanced you can choose how specific the fuzzy matching has to be. Then if you wanted to make a separate table, with possible exceptions in the merge you can add it as well.

 

[M0X3]

Correct, if you load both sets of data into power query and then merge them, you can click the box for fuzzy matching. then under advanced you can choose how specific the fuzzy matching has to be. Then if you wanted to make a separate table, with possible exceptions in the merge you can add it as well.

What version of Excel first got fuzzy merge? I posted another thread about this but I've been unable to use fuzzy match on my Excel 2016 software even despite following the exact instructions on several tutorials. The fuzzy match button is always missing!

 

[daregu]

What version of Excel first got fuzzy merge? I posted another thread about this but I've been unable to use fuzzy match on my Excel 2016 software even despite following the exact instructions on several tutorials. The fuzzy match button is always missing!

Sorry, I just realized your version of excel is 2016. Apparently there is an add in available for some functionality, although I can't speak to how well it works. I definitely recommend the 365 version if that is as an option. It has many amazing features over the 2016 version.

 

[ZVI]

Hi,
For your version of Excel 2016 the Fuzzy Lookup AddIn can be used.
Learn how to use it in this video of Bill Jellen (Mr Excel) - Fuzzy Lookup in Excel from Microsoft Labs - Episode 2271