Comparing two columns using Match Function (Approximate)

RoxyWincott

New Member
Joined
Oct 18, 2017
Messages
3
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!?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0
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?
 
Upvote 0
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)


 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top