Consolidating daily data series by date

Elvis

New Member
Joined
Apr 23, 2006
Messages
45
Hello there,

I have several excel worksheets that I want to consolidate. In each sheet, column A contains a daily date field; column B contains the value for those dates.

But the length of each series is not the same, some series could start in 1998, others in 2004 and even when the periods covered overlap, not every date will have a value for all the series.

I need to create a summary sheet of all this data grouping by date. So in the summary sheet, Column A would have the dates and then each successive column (B, C D E etc) would have the data values associated for the given date. Where a particular series does not have a value for a given date, I want that particular cell to be populated with the value “-“or left blank.

To help acheive this task, in the summary sheet I could create a date field covering every potential date that each series could have. Then I guess I would just need some kind of code or look up function that would bring in the data frpom the individual sheets aligned by date.

Note - To-date, I have been consolidating the data by importing each series/worksheet into Access and then joining each series in a query using the data field, and copy the results into excel. A very manual and time consuming process, also now the number of series have increased dramaticly.

Thanks

The King
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Elvis

New Member
Joined
Apr 23, 2006
Messages
45
Hello,

Further to my post below a few days ago, does anyone have any idea how I could automate what I want to do?

Any thoughts appreciated.

Thanks,

Elvis
 

Watch MrExcel Video

Forum statistics

Threads
1,114,051
Messages
5,545,724
Members
410,702
Latest member
clizama18
Top