Need help for a VBA code for my master thesis - a bit of a challenge

[DanielIcke]

Hey guys,

I am new to this forum and new to advance excel functions.
Currently, I am writing my master thesis and have found many of the tasks to be extremely cumbersome and time consuming when wanting to do it manually. Currently I am facing the following problem that I hope that you can help me with.

Currently, I have identified a completion date of an M&A-deal, which can be found in column F and +920 rows. I column Column O the fiscal year of the deals occurrence has been identified. In column Q to AF, the dates of when fiscal years 1998 to 2013 ends are listed.

What I now need to do is to identify how many deals by the same firm (in another workbook) are:

- in the fiscal year prior (preceding) to this deals fiscal number (Column O)
- More one year prior (preceding) to the identified deals fiscal year
- One year proceeding the year of the identified deals
- Two years proceeding the year of the identified deals
- Three years proceeding the year of the identified deals

The unique number on which to search on is the companies ISIN number and the completion dates of their deals.
In the workbook I need the answers in and where the completion date of the M&A deals that I need to find the information for are listed, the ISIN numbers are identified in Column D.

In the workbook (reference workbook) where one must search for the other deals and when these occur in terms of fiscal years (-1, +1 +2 +3) the ISIN numbers are found in Column U and the completion dates of the firms other deals (if any are) column M

I hope that you understand the description. I can take some screenshot of my to workbooks if needed where you can see the columns and rows and their titles.
I will not be able to upload the complete sheets and wookrbooks as they are +100MB. This is due to the fact that the reference workbook that must be "scanned throught" contains +27000 deals.

This is also why manually identifying them will be a hassle. If you can only help with parts of the problem, then that is equally fine - I just need to make the process a little bit more dynamic and find it difficult to comprehend how.

I hope that you can help me out - please do not hesitate to ask for more information if you need it. There might be a slight delay as I live in Denmark (+1 GMT).

Kind Regards,
Daniel Icke

 

[DWig]

Screenshots would be helpful. I can mostly follow, but I'd like to be sure before suggesting an answer.

 

[Rick Rothstein]

Screenshots would be helpful. I can mostly follow, but I'd like to be sure before suggesting an answer.

Following up to DWig's comment, see my signature at the bottom of this message for ways to include screen shots in your posts.

 

[DanielIcke]

<thead>
</thead><tbody>
</tbody>

The excel file with the sample [TABLE]
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[TH]K[/TH]
[TH]L[/TH]
[TH]M[/TH]
[TH]N[/TH]
[TH]O[/TH]
[TH]P[/TH]
[TH]Q[/TH]
[TH]R[/TH]
[TH]S[/TH]
[TH]T[/TH]
[TH]U[/TH]
[TH]V[/TH]
[TH]W[/TH]
[TH]X[/TH]
[TH]Y[/TH]
[TH]Z[/TH]
[TH]AA[/TH]
[TH]AB[/TH]
[TH]AC[/TH]
[TH]AD[/TH]
[TH]AE[/TH]
[TH]AF[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]1[/TD]
[TD]Name in Zephyr (A-Z)[/TD]
[TD]ISIN Code in DataStream[/TD]
[TD]ISIN Code in Zephyr[/TD]
[TD]BvD in Zephyr[/TD]
[TD]Completed Date[/TD]
[TD]Possibly also a Target (checked on BvD and ISIN)[/TD]
[TD]Becomes Target In Date[/TD]
[TD]Deals in the PF-time frame that do not live up to FDI criteria (>10%)[/TD]
[TD]Completed > -1[/TD]
[TD]Completed T=minus 1[/TD]
[TD]Completed T= +1 or +2[/TD]
[TD]Completed T=+3[/TD]
[TD]After T=+3[/TD]
[TD]Final Accounting Year Code - T=0 - Manual[/TD]
[TD]Accounting Year Code[/TD]
[TD="align: right"]1998[/TD]
[TD="align: right"]1999[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2001[/TD]
[TD="align: right"]2002[/TD]
[TD="align: right"]2003[/TD]
[TD="align: right"]2004[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]2013[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]TELEKOM AUSTRIA AG[/TD]
[TD]AT0000720008(WC05350)[/TD]
[TD="bgcolor: #FFFFFF"]AT0000720008[/TD]
[TD="bgcolor: #FFFFFF"]AT9110245031[/TD]
[TD="bgcolor: #FFFFFF, align: right"]12-07-2005[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]No[/TD]
[TD="align: right"]4[/TD]
[TD]None[/TD]
[TD="align: right"]31-05-2007[/TD]
[TD="align: right"]31-03-2008[/TD]
[TD="bgcolor: #FFFFFF, align: right"]22-01-2009[/TD]
[TD="align: center"]2005[/TD]
[TD="align: right"]2005[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-1998[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-1999[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2000[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2001[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2002[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2003[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2004[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2005[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2006[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2007[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2008[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2009[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2010[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2011[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2012[/TD]
[TD="align: right"]#I/T[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="bgcolor: #F2F2F2"]UPDATE SOFTWARE AG[/TD]
[TD]AT0000747555(WC05350)[/TD]
[TD="bgcolor: #F2F2F2"]AT0000747555[/TD]
[TD="bgcolor: #F2F2F2"]AT9110047829[/TD]
[TD="bgcolor: #F2F2F2, align: right"]26-04-2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]No[/TD]
[TD="align: right"]2[/TD]
[TD]None[/TD]
[TD]None[/TD]
[TD]None[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2007[/TD]
[TD="align: right"]2007[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-1998[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-1999[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2000[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2001[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2002[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2003[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2004[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2005[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2006[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2007[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2008[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2009[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2010[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2011[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2012[/TD]
[TD="align: right"]#I/T[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="bgcolor: #F2F2F2"]BRAIN FORCE HOLDING AG[/TD]
[TD]AT0000820659(WC05350)[/TD]
[TD="bgcolor: #F2F2F2"]AT0000820659[/TD]
[TD="bgcolor: #F2F2F2"]AT9110050653[/TD]
[TD="bgcolor: #F2F2F2, align: right"]06-07-2007[/TD]
[TD]Yes[/TD]
[TD="align: right"][/TD]
[TD]Yes but not of influence, because here are other deals which are above the criteria - some at 100%[/TD]
[TD="align: right"]7[/TD]
[TD="bgcolor: #FFFF00, align: right"]29-09-2006[/TD]
[TD="align: right"][/TD]
[TD="align: right"]26-02-2010[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2007[/TD]
[TD="align: right"]2007[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-1998[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-1999[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2000[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2001[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2002[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2003[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2004[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2005[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2006[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2007[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2008[/TD]
[TD="align: right"]30-09-2009[/TD]
[TD="align: right"]30-09-2010[/TD]
[TD="align: right"]30-09-2011[/TD]
[TD="align: right"]30-09-2012[/TD]
[TD="align: right"]#I/T[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]BEKO HOLDING AG[/TD]
[TD]AT0000908603(WC05350)[/TD]
[TD="bgcolor: #FFFFFF"]AT0000908603[/TD]
[TD="bgcolor: #FFFFFF"]AT9150067440[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30-09-2008[/TD]
[TD]Yes, buys Brain Force Holding AG above with 47% from an initial stake of 6%[/TD]
[TD="align: right"][/TD]
[TD]No[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]None[/TD]
[TD="align: right"]None[/TD]
[TD="align: right"]None[/TD]
[TD="align: right"]None[/TD]
[TD="align: center"]2008[/TD]
[TD="align: right"]2008[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-1998[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-1999[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2000[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2001[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2002[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2003[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2004[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2005[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2006[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2007[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2008[/TD]
[TD="align: right"]30-09-2009[/TD]
[TD="align: right"]30-09-2010[/TD]
[TD="align: right"]30-09-2011[/TD]
[TD="align: right"]30-09-2012[/TD]
[TD="align: right"]#I/T[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF"]VOESTALPINE AG[/TD]
[TD]AT0000937503(WC05350)[/TD]
[TD="bgcolor: #FFFFFF"]AT0000937503[/TD]
[TD="bgcolor: #FFFFFF"]AT9070005375[/TD]
[TD="bgcolor: #FFFFFF, align: right"]26-10-2004[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Yes, but others that live up to the requirement also give overlap, and these are above 51% percent acquired - not initial stake. [/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]None[/TD]
[TD="bgcolor: #FFFF00, align: right"]31-12-2006[/TD]
[TD]Many[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2005[/TD]
[TD="align: right"]2005[/TD]
[TD="bgcolor: #FFFF00, align: right"]31-03-1998[/TD]
[TD="bgcolor: #FFFF00, align: right"]31-03-1999[/TD]
[TD="bgcolor: #FFFF00, align: right"]31-03-2000[/TD]
[TD="bgcolor: #FFFF00, align: right"]31-03-2001[/TD]
[TD="bgcolor: #FFFF00, align: right"]31-03-2002[/TD]
[TD="bgcolor: #FFFF00, align: right"]31-03-2003[/TD]
[TD="bgcolor: #FFFF00, align: right"]31-03-2004[/TD]
[TD="bgcolor: #FFFF00, align: right"]31-03-2005[/TD]
[TD="bgcolor: #FFFF00, align: right"]31-03-2006[/TD]
[TD="bgcolor: #FFFF00, align: right"]31-03-2007[/TD]
[TD="bgcolor: #FFFF00, align: right"]31-03-2008[/TD]
[TD="bgcolor: #FFFF00, align: right"]31-03-2009[/TD]
[TD="bgcolor: #FFFF00, align: right"]31-03-2010[/TD]
[TD="bgcolor: #FFFF00, align: right"]31-03-2011[/TD]
[TD="bgcolor: #FFFF00, align: right"]31-03-2012[/TD]
[TD="align: right"]#I/T[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF"]POLYTEC HOLDING AG[/TD]
[TD]AT0000A00XX9(WC05350)[/TD]
[TD="bgcolor: #FFFFFF"]AT0000A00XX9[/TD]
[TD="bgcolor: #FFFFFF"]AT9070110800[/TD]
[TD="bgcolor: #FFFFFF, align: right"]22-10-2008[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]No[/TD]
[TD]None[/TD]
[TD="align: right"]03-07-2007[/TD]
[TD]None[/TD]
[TD]None[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2008[/TD]
[TD="align: right"]#I/T[/TD]
[TD="align: right"]#I/T[/TD]
[TD="align: right"]#I/T[/TD]
[TD="align: right"]#I/T[/TD]
[TD="align: right"]#I/T[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2002[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2003[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2004[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2005[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2006[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2007[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2008[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2009[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2010[/TD]
[TD="bgcolor: #8DB4E3, align: right"]31-12-2011[/TD]
[TD="align: right"]#I/T[/TD]
[TD="align: right"]#I/T[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Final.ACC.Year.ACQ.[/B][/COLOR][/CENTER]
The reference workbook, where to make the check for other deals:
[IMG][TABLE]
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]C[/TH]
[TH]E[/TH]
[TH]M[/TH]
[TH]N[/TH]
[TH]O[/TH]
[TH]S[/TH]
[TH]T[/TH]
[TH]U[/TH]
[TH]V[/TH]
[TH]W[/TH]
[TH]X[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: #96A3B5"]Acquiror name[/TD]
[TD="bgcolor: #96A3B5"]Target name[/TD]
[TD="bgcolor: #96A3B5, align: right"]Completed date[/TD]
[TD="bgcolor: #96A3B5"]Initial stake (%)[/TD]
[TD="bgcolor: #96A3B5"]Acquired stake (%)[/TD]
[TD="bgcolor: #96A3B5"]Acquiror BvD ID number[/TD]
[TD="bgcolor: #96A3B5"]Target BvD ID number[/TD]
[TD="bgcolor: #96A3B5"]Acquiror ISIN number[/TD]
[TD="bgcolor: #96A3B5"]Target ISIN number[/TD]
[TD="bgcolor: #96A3B5"]Acquiror primary US SIC code[/TD]
[TD="bgcolor: #96A3B5"]Target primary US SIC code[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFF00"]HUNTER DOUGLAS NV[/TD]
[TD="bgcolor: #FFFF00"]ECLIPSE BLIND SYSTEMS LTD[/TD]
[TD="bgcolor: #FFFF00, align: right"]03-09-2002[/TD]
[TD="bgcolor: #FFFF00"]0.00 0.00 0.00 0.00[/TD]
[TD="bgcolor: #FFFF00"]100.00 100.00 100.00 100.00[/TD]
[TD="bgcolor: #FFFF00"]NL24117994[/TD]
[TD="bgcolor: #FFFF00"]GBSC047712[/TD]
[TD="bgcolor: #FFFF00"]ANN4327C1220[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00"]2591[/TD]
[TD="bgcolor: #FFFF00"]2591 2591 2591 2299[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]PF.Cluster.Check[/B][/COLOR][/CENTER]



That you both so much for your help. I will close down for tonight. It is getting late in Denmark.
Hope that you will have a proposition for when I wake up tomorrow.

The kindest regards,
Daniel Icke

 

[DanielIcke]

Hey guys,
Is it possible? Maybe just breaking it down into incremental steps?

Looking forward to hearing your ideas.

 

[DanielIcke]

Any ideas?

 

[DanielIcke]

Hey again guys. I guess that the problem which I have posted is too comprehensive and cumbersome. I am however still interested in whether I can find a code to do some of the aspects quicker, instead of having to manually go through it.

Could anyone make a suggestion as to a code in which, the sample firms are identified in the reference workbook and where all the deals by the sample firms are transferred to another sheet. So only the deals that are of interest are apparent, instead of 27000. Then 915 X all the deals conducted by the unique firm (use ISIN and BvD code to identify).

The Kindest Regards,

Daniel Icke