I have two databases that house similar information that I'm attempting to join/merge together. The main join takes place between Company Name, and there is not always consistency with the company names, even within the same database. I'm attempting to create a common Company Name between the two databases so that I can use Company Name (along with a few other fields) to join the two sources. Here is an example:
Database 1
| Company Name | Event Date | Notes | Contact |
| XYZ Retirement Communities | 1/1/2019 | | Jane Doe |
| XYZ - Retirement Communities | 7/1/2019 | At Location Y | Jane Doe |
| XYZ Retirement Communities | 12/1/2019 | | Jim Smith |
| 123 LLC | 1/1/2020 | | |
Database 2
| Company Name | Event Date |
| XYZ Retirement Communities | 1/1/2019 |
| XYZ Retirement Community | 7/1/2019 |
| 123 LLC | 2/1/2020 |
Current Efforts
My original efforts were to join the tables together, then remove all duplicate names, leaving me with just a list of unique Company Names, and then use a fuzzy match. What this left me was the following:
| Company Name | Match Name |
| XYZ Retirement Communities | XYZ Retirement Community |
| XYZ - Retirement Communities | XYZ Retirement Communities |
| XYZ Retirement Community | XYZ Retirement Communities |
| 123 LLC | 123 LLC |
What I'm seeing with my results is that I end up with multiple matches that don't always tie together. In this case, my hope was that this match would result in the same Match Name for all 3 original Company Names, but instead I'm showing more than 1 Match Name.
Goal
Looking at Company Name, there are three separate versions of XYZ Retirement Communities. All of these separate versions are the same company, and there should be a common name both to join the tables, and to ensure that within the individual tables, we have the same Company Name. My thoughts were to create a new field for each table called "Common Company Name" that would be used to then join the databases. M
