This is a very complex problem I'm facing!! I'm a student, and I need to solve it if I want to move on
with my Master's thesis!! I promise a beer (or at least the $ to get it) to the one that will save me several days of manual
IN SHORT: I need to calculate the industry relatedness between the acquisitions of a specific acquiring
1st: I have a list of acquisitions (identifiable by a specific date, a specific acquirer's code, and a
specific industry code)
2nd: for each acquisition (focal acquisition), I need to look at the other acquisitions done by the same acquirer in the 5
years prior to the focal acquisition
- if the industry code (4-digit code) of the focal acquisition matches the industry code of one of the previous acquisitions
in the last 5 years, I will give a 6
- if only the first 3 digits of the 4-digit code matches, I will give a 4
- if only the first 2 digits of the 4-digit code matches, I will give a 2
Here's a screen shot... I already
started calculating but manually. I'm quite sure there should be a formula.
The full set of data can be downloaded in attach: target-to-target similarity.xlsx
Example: In the
screenshot you can see LINE 34 is given 4 because LINE 34 has the industry code 4925 and LINE 36 has the industry code 4922.
I give 4 because (1) they share the same 3 first digits "492", (2) they are from the same acquirer, and (3) LINE 36
was in the 5 years prior than LINE 34
I would endlessly appreciate your help!