compare 2 columns in excel.

[Viwanou]

I would like to compare 2 columns in excel. Both columns are the name of companies.The name of companies are not all time the same. An example is: in one list I have ABBEY and in other one Abbey National.It is the name of the same company. The idea is to see which companies are present in both list. I got already some results but the matching is not perfect because I missed a lot of company which are présent in both list. I don't want to do the job manually.
This is an overview of my list1 and it ranked from 1 to 533
ABBEY ABN AMRO ACCOR ACKERMANS ACS ACTELION ADECCO ADIDAS-SALOMON AEGON ...
The second list is and it is ranked from 1 to 813
3i Group PLC 3M Co. A2A SpA AAREAL BANK ABB Abbey National ABN-AMRO Hldg Abu Dhabi National Energy Company Accell Group Accenture PLC Cl A Accor Acea Achmea Achmea Hypotheekbank Adecco ADP ADT CORP Aegon

I have my two list on the same folder. The name of my first list is Issuer and the second list is Company name.
As I want my results in colum C, so I copied ans pasted in C2 this formula =OFFSET($B$2:$B$813;MATCH(SUBSTITUTE(A2; " "; "*"); $B$2:$B$813; 0)-1;0;1;1) It is worked partially. I missed always companies like ABN-AMRO. Why?
I hope to find some help here

 

[Hui]

Viwanou

Firstly, Welcome to the Chandoo.org Forums

Chandoo has written a number of posts about comparing lists

Start by using the Google Custom Search box at the top right of this and every page at Chandoo.org

Come back here if that doesn't assist you.

 

[Viwanou]

Hi
I already did it. I didn't find any solution. That is why I decided to ask for help

 

[srinidhi]

Viwanou, If you are sure of the first 3 or 4 letters, you can use vlookup with the starting 3 words & you will get the results when the starting words match in another list. This is a common problem most of use face in worklife.
VLOOKUP(LEFT(A1,3)&"*",B:B,1,0)
VLOOKUP(LEFT(cell,first 3 letters)&"*",reference rows,1,0)

 

[Viwanou]

Hi Srinidhi
I wrote the formula =VLOOKUP(LEFT(A1,3)&"*",$B$2:$B$813,1,0) . It doesn't work. I can't apply automaticaly the formula to the other cells. Any help?

 

[Viwanou]

Hi Srinidhi
I changed the formula =VLOOKUP(LEFT(A2,3)&"*";$B$2:$B$813;1;0) . Ecxel don't accept it. Help please. I'm lossing all my workday dealing with this problem

 

[bobhc]

Good day Viwanou

Have a look at the attachment it may help you

 

[SirJB7]

Hi, Wiwanou!
It's logic that it doesn't work for ABN-AMRO since in 1st list it's written as ABN AMRO. Search problems using approximated matching are very difficult to tweak, if not impossible, since there are a lot of special cases to take care of.
The short list that you provided might lead to formulas that work for it but not to the full extent of your actual data, so consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!