Merging two datasets with unclear identifier in Excel I'm trying to merge two datasets on Mergers &amp; Acquisitions. They both consist of c.10'000 observations with c.50-100 variables each. One contains information about the actual M&amp;A deal whereas the other one contains info on how a deal was financed.

The problem is that there is no clear and unique identifier. For example, I could use the date that the deal was announced but that wouldn't be unique because on some days 10 deals were announced. Using company names is difficult since they mostly aren't identical in both datasets. For example if in one dataset I find "Ebay", in the other the same company could be called "eBay", "Ebay Inc", or "Ebay, Inc."

I've been working with the Fuzzy Lookup add-on for Excel, as well as concacenating various identifiers that are not unique but in their combination become useful (e.g. Date &amp; Country &amp; SIC Industry Classification Code, etc.). However I haven't been able to generate as many matches as I would have hoped.

I'd be grateful for any ideas or pointers towards resources that would help me merge the datasets more efficiently.
I typically proceed the following way,


convert to the same case with LOWER() or UPPER();
remove all non-alphanumeric characters with several SUBSTITUTE(), e.g. SUBSTITUTE(A1, ".", "");
trim white spaces TRIM();
extract the first word with LEFT(A1, FIND(" ", A1) - 1);
trim white spaces again;
perform a join on the key that you created.


Often this gives you a decent match.