I have 2 databases, both have names of companies, but in different formats. I have been able to do exact matching using vlookup
. I want to extract companies that were written differently, but they are actually the same company and extract the data.
Below is a small part of the databases I have
Database 1
Column A
1-800-Flowers.com Inc
Abbott Laboratories (Abbott)
21st Century Fox America Inc (formerly News America Inc)
Column B
1234(data I need to grab)
4567
8910
Database 2
Column C
1-800 CONTACTS INC
1-800-FLOWERS.COM
ABBOTT LABORATORIES
TWENTY-FIRST CENTURY FOX INC
Column D
ABCD(DataI can ignore as the company doesn't exist in database 1)
EFGH (Data I need as it matches from Database 1)
IJK
LMNO
As you can see from the above databases, Database 1 matches Database 2's in similar words like 21st Century Fox America Inc vs Twenty-first Century Fox Inc
In my database 1, I have about 4000+ values, while in database 2, I have 10,000 values. Is there a code to compare similar words between both databases and extract the data I need from columns B and D?
I have tried query, but it doesn't work the way I wanted it to. This is my shareable link.
Currently, What I have done is to extract the words which are similar using REGEXTRACT to find a match between the strings like Century Fox in 21st Century Fox and Twenty-First Century Fox and attempted to match both data sets using query. However my query result comes up with NA when I write it like this
=query(E:E,"Select E where E contains '"&L2&"'",0 )
L2 being the cell that contains the string Century Fox
See Question&Answers more detail:os