I'm trying to merge two datasets on Mergers & Acquisitions. They both consist of c.10'000 observations with c.50-100 variables each. One contains information about the actual M&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 & Country & 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.

  • $\begingroup$ Why does the combination of individually non-unique not help you? Does it become unique or not? $\endgroup$ – sheß Aug 23 '15 at 20:11
  • $\begingroup$ I haven't gotten any combinations to work well enough: Either they are not unique or they are but filter out a lot of matches that they shouldn't due to different notations in the two datasets $\endgroup$ – F Bert Aug 24 '15 at 9:27
  • $\begingroup$ Are you really tied to using Excel? Are you also going to use Excel in your ultimate analysis? I think if you want to avoid using something else, @Stereo's answer, or maybe a slightly modified version is what you'd have to go for $\endgroup$ – sheß Aug 24 '15 at 9:30
  • $\begingroup$ Well, Excel is the only tool I'm familiar with. What tool would you recommend for a beginner? $\endgroup$ – F Bert Aug 24 '15 at 17:52

I typically proceed the following way,

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

Often this gives you a decent match.


Use google search, which has some very sophisticated similarity algorithms, to lookup the company names and see if the point to the same web site addresses using python, mathematica or import.io or a tool of your choice.

  • $\begingroup$ This is among the things I've been doing manually, however with two sets of 10k observations each it's very time consuming. Do you have any idea on how this could automated? $\endgroup$ – F Bert Aug 23 '15 at 19:45
  • 1
    $\begingroup$ Sorry, I hadn't considered anyone would try to do this manually, $\endgroup$ – image_doctor Aug 24 '15 at 0:16
  • $\begingroup$ Yes I've added to the answer a few tools I would consider to automate this, if you have data we could experiment with maybe solutions would be tractable. $\endgroup$ – image_doctor Aug 24 '15 at 12:36
  • $\begingroup$ Thanks. The data stems from commercial data providers so I'm not sure I'd be allowed to share it, unfortunately. I haven't worked with any of the tools you've mentioned - which would you recommend for a beginner? $\endgroup$ – F Bert Aug 24 '15 at 18:00
  • $\begingroup$ import.io - it's designed to require little or no programming $\endgroup$ – image_doctor Aug 24 '15 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.