Thread: Matching data from one sheet to another based on cusips

Hello,

I have to merge data between sheets and I want to avoid a lot of manual work. So here is my problem:

In the Excel file, I refer to the first sheet and the 6digit Cusips. The cusips are company identifiers and represent firms that introduced antitakeover provisions, such as classified boards, over the period from 1997 to 2009. Classified Boards is an independent governance variable that I use in my analysis. Most of the cusips appear several times for one firm, because they refer to the different years from 1997 to 2009.

In the second, I have other control variables. They measure size, leverage, risk. In the third sheet I have the dependent variable profitabiity measured by e.g. Operating profit margin. The 6digit cusips (Cusip6) are mentioned as well. As not all firms introduced antitakeover provisions, the first sheet does not have all cusips of Sheet 2 and 3.

I want to merge the data from sheet 2 and 3 according to the Cusisp of the first sheet. Then I want to import the data into Stata to do the regression.

Does somebody now a function how to do that in Excel? (I attached a small part of the file due to a size limit for attachments)

Thanks...

hey thx for the help so far, however, it is still not working...probably because I am not very experienced with Excel

....instead of B:B, the index formula in Excel tells me to type in row number and column number, so 2,2 in my case, but excel does not calculate it

and what do you mean with D:D, 1?

regards

hey thx a lot, it works now..
however the next problem is already waiting for me I guess^^

Hello,

in the file attachted the data are collected for governance variables (e.g. Classified Board) every two years, the even years. For instance, I need the data from 1998 to be copied to the year 1999 and this for all firms/Cusips. Thus, I need the same entries from of the year 1998 in year 1999. I only need to change the year to "1999", the rest of the data from 1998 should stay the same.
I have to do that for many firms. Is there a function how to do that?

In addition to that, I also need to copy the data for Beta, Total Assets and Total LTDebt from the 2nd and 3rd sheet which is typed in horizontally, to be copied to the 1st sheet.
However, in the first sheet the data have to be vertically, according to the years on the left side in sheet 1.

Is there another function to do that? Or maybe it works like the Index and Matching formula indicated already above...I will try that as well!

see the file attached here