LucasLondon
11-29-2006, 08:46 AM
Hello there,
I have several excel worksheets that I want to consolidate into one. In each sheet, column A contains a daily date field and column B contains the values associated for thoose 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 in all the sheets/for all the series.
I need to create a summary sheet of all this data grouped 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 that given date from respective sheets. 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.
So for example, say the individual sheets contain the following data:
Sheet 1
Col A Col B
11/11/2006 4
12/11/2006 5
13/11/2006 6
Sheet 2
Col A Col B
11/11/2006 19
13/11/2006 20
The summary sheet would collate all the data together as follows (assuming sheet 1's data goes into column b and sheet 2's data into col C:
Summary Sheet
Col A Col B COL C
11/11/2006 4 19
12/11/2006 5 -
13/11/2006 6 20
To help achieve 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 from the individual sheets aligned by date.
Any help much appreciated!
I have several excel worksheets that I want to consolidate into one. In each sheet, column A contains a daily date field and column B contains the values associated for thoose 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 in all the sheets/for all the series.
I need to create a summary sheet of all this data grouped 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 that given date from respective sheets. 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.
So for example, say the individual sheets contain the following data:
Sheet 1
Col A Col B
11/11/2006 4
12/11/2006 5
13/11/2006 6
Sheet 2
Col A Col B
11/11/2006 19
13/11/2006 20
The summary sheet would collate all the data together as follows (assuming sheet 1's data goes into column b and sheet 2's data into col C:
Summary Sheet
Col A Col B COL C
11/11/2006 4 19
12/11/2006 5 -
13/11/2006 6 20
To help achieve 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 from the individual sheets aligned by date.
Any help much appreciated!