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!?
 

RoxyWincott

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

RoxyWincott

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

Well-known Member
Joined
Jan 17, 2015
Messages
873
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)


 

Forum statistics

Threads
1,081,991
Messages
5,362,595
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top