Comparing two columns using Match Function (Approximate)

[RoxyWincott]

I am trying to compare two columns, each of which contain company names. The list is over 6,000 rows so I would like to use a function and drag it down. Normally, you would use the Match function, but the problem is the company names are slightly different.

Example: Column A Column B
ABC Company ABC Comp, Inc.
Dwelling Corporation Dwelling Corp A.S
Sterling Pups Inc. Sterlingpups incorporated

They are slightly different company names, but they are the same company. I've tried using the Match function with "*"& and left function that will compare the first 5 or however many letters from the left. This gets missed up also, since for example in the first one if you compare the first 9 letters it will not mark it as a match. After Comp, it changes so it would not match them.

I've heard of Fuzzy Lookup, but wasn't sure if you can get the results I'm looking for with that. I just want to compare the two columns and see if there is a match. Ultimately I want the percent of overlap between the two columns. Can someone please help!?

 

[admiral100]

Re: Comparing two columns using Match Function (Aprroximate)

Hi,

Look the VBA code

https://www.extendoffice.com/docume...ngs-highlight-differences-for-similarity.html

 

[RoxyWincott]

Re: Comparing two columns using Match Function (Aprroximate)

Thank you Admiral100, the only issue with the VBA code is that the companies must be next to each other (same row) for it to work. These companies are scattered throughout the column. In the example below, if they were side by side, Doggy Dog, Inc. the first one in column A is also connected to the 3rd down in column B.

Example:

Column A

Doggy Dog, Inc
Verperi
Global Anti-Draft
Lazertech Company

Column B

Aspirinal
Bazerlight A.B
Doggy Dog Incorporated
Grupco Centre

 

[admiral100]

Re: Comparing two columns using Match Function (Aprroximate)

Try this : copy the code from post 25

https://www.mrexcel.com/forum/excel...-matching-new-version-plus-explanation-3.html

Then enter in C1 =fuzzyvlookup(A1,B1:B4,1,,,1)

 

[RoxyWincott]

Re: Comparing two columns using Match Function (Aprroximate)

Thank you! I will try this. I am a bit of a newby here.... can you fill me in on where I should put that long code from post 25?

 

[admiral100]

Re: Comparing two columns using Match Function (Aprroximate)

Alt + F11
Toolbar: Insert - Module
Paste code in the Editor window for that module
Click the top-most-Right Close "X" to return to the sheet.

Then enter in C1 =fuzzyvlookup(A1,B1:B4,1,,,1)