Re: Help on joining/merging one to many

factorhedge · Posted 04-19-2013 10:14 AM

My datasets looks like this

1) It has many columns but for simplicity sake I have mentioned 3 columns

UniqueCompanyCode	Date	CompanyName

2)

UniqueCompanyCode	Date	MarketCap	ClosingPrice

I would like to create a table by joining/merging the above two tables based on ' UniqueCompanyCode' and 'Date' I would want the output like this . Essentially table 1 is monthly data for 12 years(there are 144 different sreadsheets) and it has many other columns and table 2 is one spreadsheet having all the companies data monthly for 12 years. I am looking to augment the MarketCap and ClosingPrice date from table 2 to table 1

UniqueCompanyCode	Date	MarketCap	ClosingPrice

For example if the date in table 1 is 28/02/2012 and UniqueCompanyCode is " 1234 " than I would want merge the respective data from table 2

I would like to know the code for doing for 1 month , after which I guess I will have to create a ' do loop ' for all other sheets.

Reeza · Posted 04-19-2013 10:56 AM

Bring in your monthly data and append it all together first. Then merge it all at once with table 2.

1. Import all your spreadsheets (there are ways to automate many imports).

2. Append them together.

3. Merge once with Table 2.

This way you separate your import and merge process and it helps to debug things faster.

factorhedge · Posted 04-20-2013 11:08 AM

Thanks Reeza , very helpful , however before I append all the sheets I just wanted to see whether the code suits my required output. There are around 155 such sheets with 35k rows in each and 13-15 columns.

Perhaps I will do as you said , that is I have a macro to import all sheets then will append and merge it using by. I will have to merge it by dates and Companycodes.