Hamond
01-17-2009, 09:49 AM
Hi,
First apologies for the long post, I'm trying to provide as much detail as possible so you can quickly get the picture of what I'm trying to do and the challenges.
Currently I have several data series/variables in a single worksheet, in blocks of two columns, the first column as a date for the given series (month) and the second column the actual values of the series.
The next two column has the date and values respectively for the next series. There are no empty columns between blocks/series:
Col A Col B Col C Col D
31/01/1980 40.2441 31/10/2001 -16.98
29/02/1980 45.3308 30/11/2001 -21.81
The actual data/dates start in row 3, row 1 and 2 contain headings. The last populated column is BO and the last populated row is 356. Each series has a different length of history hence the number of rows populated for each series differs. However, no one series will have any unique dates other than that accounted for by differences in the length of the time series so for example two series with exactly the same number of months/rows populated will share exactly the same dates.
I would like to consolidate/group the data by Date thereby removing any duplicate dates. I'm looking for the most efficient solution. Yes I could put the data into a pivot table but for this approach to work, I would need to insert and populate additional columns as series identifiers and then group/stack all the data together in columns A and B for the pivot table to be able to work. This seems like a lot of work.
Alternatively, I was thinking would some kind of automated lookup for each block of data work? So for example, in a new sheet, in col A I could insert all possible dates that could occur across overall all the series (e.g. by taking the dates from the series with the longest history). Then I could run a lookup based on the date for each block of data and bring in the corresponding values based on the dates in column A of the new consolidated sheet. But obviously if I did this manually, then I would have to change the lookup range for each block of data which would take some time so I'm hoping someone can suggest a way to automate the process!
But first does anyone think this approach would work and would be quick to implement? Or does anyone have alternative suggestions (other than the pivot table route which would require some work as well).
With my approach I'm thinking, with the vlookup function would be defined as follows:
First Block =VLOOKUP($A2,'Raw Data'!A$4:B$356,2, FALSE)
Second Block =VLOOKUP($A2,'Raw Data'!c$4:d$356,2, FALSE)
and so fourth.
The row ranges could be hardcoded as above, however the column lookup ranges would need to change for each block. And the vlookup results in the summary sheets would need to change by one column as the lookup runs across each block. I'm hoping someone can help me to automate so that the above process can be run as some kind of macro where the look up function sits in the code and the varaable parameters change accordingly as it loops through each block of data.
Hope someone can help.
Many thanks,
Hamond
First apologies for the long post, I'm trying to provide as much detail as possible so you can quickly get the picture of what I'm trying to do and the challenges.
Currently I have several data series/variables in a single worksheet, in blocks of two columns, the first column as a date for the given series (month) and the second column the actual values of the series.
The next two column has the date and values respectively for the next series. There are no empty columns between blocks/series:
Col A Col B Col C Col D
31/01/1980 40.2441 31/10/2001 -16.98
29/02/1980 45.3308 30/11/2001 -21.81
The actual data/dates start in row 3, row 1 and 2 contain headings. The last populated column is BO and the last populated row is 356. Each series has a different length of history hence the number of rows populated for each series differs. However, no one series will have any unique dates other than that accounted for by differences in the length of the time series so for example two series with exactly the same number of months/rows populated will share exactly the same dates.
I would like to consolidate/group the data by Date thereby removing any duplicate dates. I'm looking for the most efficient solution. Yes I could put the data into a pivot table but for this approach to work, I would need to insert and populate additional columns as series identifiers and then group/stack all the data together in columns A and B for the pivot table to be able to work. This seems like a lot of work.
Alternatively, I was thinking would some kind of automated lookup for each block of data work? So for example, in a new sheet, in col A I could insert all possible dates that could occur across overall all the series (e.g. by taking the dates from the series with the longest history). Then I could run a lookup based on the date for each block of data and bring in the corresponding values based on the dates in column A of the new consolidated sheet. But obviously if I did this manually, then I would have to change the lookup range for each block of data which would take some time so I'm hoping someone can suggest a way to automate the process!
But first does anyone think this approach would work and would be quick to implement? Or does anyone have alternative suggestions (other than the pivot table route which would require some work as well).
With my approach I'm thinking, with the vlookup function would be defined as follows:
First Block =VLOOKUP($A2,'Raw Data'!A$4:B$356,2, FALSE)
Second Block =VLOOKUP($A2,'Raw Data'!c$4:d$356,2, FALSE)
and so fourth.
The row ranges could be hardcoded as above, however the column lookup ranges would need to change for each block. And the vlookup results in the summary sheets would need to change by one column as the lookup runs across each block. I'm hoping someone can help me to automate so that the above process can be run as some kind of macro where the look up function sits in the code and the varaable parameters change accordingly as it loops through each block of data.
Hope someone can help.
Many thanks,
Hamond