Consolidating multiple series in worksheet by Date field

[Hamond]

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

[mdmackillop]

Can you post a workbook with sample data?

[Hamond]

Hi,

Please find attached an example with a sample of the raw data (not all series are included) and vlookup set up in summary sheet to pull in relevant data.

Thanks

Hamond

[mdmackillop]

Give this a try
[VBA]Option Explicit
Sub Data()
Dim i As Long, Col As Long, Rw As Long, MxRw As Long
With Sheets("Raw Data")
For i = 1 To 67 Step 2
'Copy headings
.Cells(1, i + 1).Resize(2).Copy Sheets("Summary").Cells(1, (i + 1) / 2 + 1)
'Get maximum list of dates
MxRw = .Cells(Rows.Count, i).End(xlUp).Row
If MxRw > Rw Then
Rw = MxRw
Col = i
End If
Next
'Copy dates
.Columns(Col).Copy Sheets("Summary").Range("A1")
End With
'Insert LookUp formulae
With Sheets("Summary")
Range(.Cells(3, 2), .Cells(MxRw, 35)).FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC1,'Raw Data'!C1:C68,2*COLUMN()-2,FALSE)),"""",VLOOKUP(RC1,'Raw Data'!C1:C68,2*COLUMN()-2,FALSE))"
End With
End Sub
[/VBA]

[Hamond]

Hi Mdmackillop,

Thanks very much for the code. The copying of the headings and dates works fine.

However I think there is an issue with the v lookup formula as other than for the first series/block in columns A & B, the data for the remaining series is not being pulled in correctly. Right now it just brings in the data exactly how it is currently ordered/alligned in the raw data sheet instead by the date.

For example it showing data for the 31/10/1979 for all series even though no data exists for some of these series.

Hamont

[mdmackillop]

Sorry, not concentrating
Try
[VBA]'Insert LookUp formulae
With Sheets("Summary")
Range(.Cells(3, 2), .Cells(MxRw, 35)).FormulaR1C1 = _
"=VLOOKUP(RC1,OFFSET('Raw Data'!C1:C2,,2*COLUMN()-4),2,FALSE)"
End With

[/VBA]

to avoid N/A

[VBA]'Insert LookUp formulae
With Sheets("Summary")
Range(.Cells(3, 2), .Cells(MxRw, 35)).FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC1,OFFSET('Raw Data'!C1:C2,,2*COLUMN()-4),2,FALSE)),"""",VLOOKUP(RC1,OFFSET('Raw Data'!C1:C2,,2*COLUMN()-4),2,FALSE))"
End With[/VBA]

[Hamond]

Ok, the code works Great now, thanks.

Just one little thing, I've noticed there are a couple of dates missing in the summary sheet. This is my oversight, it appears that the column/series with the highest number of rows doesn't necccesarily capture all possible dates because it may simply have historical data going further back than the other series and end earlier while other series may start later but may have more recent data.

I'm thinking this might be easiest to fix by copying over all the dates from each block in a worksheet, placing them into a column, sorting and eliminating duplicate entries. And then pasting the results into column A of the summary sheet.

Would it be easy to change the existing code to accommodate for this? I'm sure I can get of the code to sort and remove duplicates and copy and paste the results in the summary sheet. But I'm not sure about the code for copying and pasting all the dates relating to each series into a worksheet.

Thanks

Hamond

[mdmackillop]

Try this version
[vba]
Option Explicit
Sub Data()
Dim i As Long, Col As Long, Rw As Long, MxRw As Long
Sheets("Summary").Cells.ClearContents
With Sheets("Raw Data")
For i = 1 To 67 Step 2
'Copy headings
.Cells(1, i + 1).Resize(2).Copy Sheets("Summary").Cells(1, (i + 1) / 2 + 1)
Next
'Copy dates
FilterDates
End With
'Insert LookUp formulae
With Sheets("Summary")
MxRw = .Cells(Rows.Count, 1).End(xlUp).Row
Range(.Cells(3, 2), .Cells(MxRw, 35)).FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC1,OFFSET('Raw Data'!C1:C2,,2*COLUMN()-4),2,FALSE)),"""",VLOOKUP(RC1,OFFSET('Raw Data'!C1:C2,,2*COLUMN()-4),2,FALSE))"
End With
End Sub

Sub FilterDates()
Dim Dt, Dts As New Collection, i As Long
Dim Rng As Range
'Get all dates
With Sheets("Raw Data")
Set Rng = Intersect(.Columns(1), .UsedRange)
For i = 3 To 67 Step 2
Set Rng = Union(Rng, Intersect(.Columns(i), .UsedRange))
Next

'Add to collection
On Error Resume Next
For Each Dt In Rng
If IsDate(Dt) Then
Dts.Add Dt, Str(Dt)
End If
Next
End With
'Write unique dates to Summary
i = 3
With Sheets("Summary")
For Each Dt In Dts
.Cells(i, 1) = Dt
i = i + 1
Next
End With
End Sub
[/vba]

[Hamond]

Hi Mdmackilliop,

Thanks a lot for the new code, works brilliantly!

I'm probably going to try to adapt the code slightly so I can use it in other instances where the number of populated columns differ. I let you know in due course if I have any problems with it. Hopefully I'll improve my VB Knowledge in the process!

Thanks,

Hamond