Combining data from two different sheets?

[Joy Pathak]

Hey guys, so I have a small problem.

I have two excel sheets. One of them has company names and years... and a set of variables with data.

I have another excel sheet with company names and years...and another set of data with years.

Is there a way for me to combined these two sheets with the data automatically getting put in columns with the appropriate years and names of companies matched?

The company names on both the sheets are different with many similar companies. I want to be able to have the similar companies that are on both the sheets matched together in a sheet.

Any help will be appreciated.

My only alternative is...going through every data...looking through...and copy pasting similar company data on one sheet......and I have 11,000 rows of company names/years.

 

[iddqd]

I guess I don't fully understand your problem but sounds like a simple vlookup?

 

[Andy Nguyen]

Use MS Query and run a Join statement. Treat data on each sheet as a table with name range. Output the query into another sheet or table.
Take a look at some info here Use MS Query to Treat Excel As a Relational Data Source, Page 1

 

[Joy Pathak]

Well....

I have..

1000 companies in a data set I got from compuSTAT with all kinds of vairables...long term debt...current assets..etc.

I have another set of companies.. 600 lets say...from SDC (TR) database with Principal ammount of debt issued.


I want to see which companies SDC database put out thats similar to the compustat companies and put the ammount of debt issued in a column next to the companies on compustat dad with the year they were issued... there are years on compustat also..so the years have to match also.

That is my dilemma. But I will look into what andy suggested. thank you.

 

[Joy Pathak]

The MS Query worked for the company names well. Unfortunately Debt issued dates were different than the compustat data.

Just trying to get the dates to match.

 

[Joy Pathak]

Alright. So...the two excel sheets company names were in different formats, and the for one year there were mutiple observations on one sheet and only one on the other. So they had to be added and put in the one cell.

Unfortunately...Going to have to add it manually per year...then join through Query all the companies with similar names...and add the ones spelt different manually.

Wow. I feel like a robot.

 

[Andy Nguyen]

Welcome to life as a financial engineer. It's the tip of things you will have to deal with data-wise (multiple sources, different formats)

 

[Joy Pathak]

It's not even the fact that I hate it or anything.

It's just....soooo....manyyyy.

I am slaving through. Once I get done, the actual "data crunching" part is only like 5 mins on SAS or PASW. But getting the data cleaned up and set up is the gruesome part.

The issue is also financial. The databases I have access to have not subscribed to the entire database. The same variables can be extracted from the 1 database but no subscription to those variables so have to take from 2 different and combine.

 

[Anup]

I've used SAS to merge datsets..... it is really simple and fast....

 

[Joy Pathak]

Yeah. The issue comes when the formats of the string variables are different. Otherwise I could just use query.

Also one database had 5 different values for each year that had to be added and matched with the year and company name on another set.

It got a bit intense.