Thread: need to align diff date ranges and frequencies

I need to align multiple time series that have different date ranges and frequencies so I can analyze and compare. I am new to coding in excel and was trying to code a macro that would insert rows and line them up.

I've attached a workbook with a small subset. I think it would be best to get them all into a weekly series..most are 28days or 7day with one that is daily.

I thought about getting them all in daily then going to weekly for analysis but might just be easier to convert all to weekly. One problem is that some of the weekly time series are weekly on a Mon, tues or Friday...they are not consistent.

I've searched this site but nothing really covered this and would appreciate any help at all!! thanks in advance!!

Hi

See if this helps.

Sub aaa()
  Dim OutSH As Worksheet
  Set OutSH = Sheets("Sheet4")
  
  OutSH.Cells.ClearContents
  
  OutSH.Range("A1").Value = "Date"
  
  Range("B5", Range("B5").End(xlDown)).Copy Destination:=OutSH.Range("A2")
  Range("F5", Range("F5").End(xlDown)).Copy Destination:=OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
  Range("J5", Range("J5").End(xlDown)).Copy Destination:=OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
  
  OutSH.Range("A:A").AdvancedFilter Action:=xlFilterCopy, copytorange:=OutSH.Range("B1"), unique:=xlYes
  OutSH.Range("A:A").Delete
  OutSH.Range("A:A").Sort key1:=OutSH.Range("A1"), order1:=xlAscending, header:=xlYes
  OutSH.Columns("A:A").AutoFit
  OutSH.Range("B1").Value = "Rates"
  
  For i = 2 To 10 Step 4
    For Each ce In Range(Cells(5, i), Cells(5, i).End(xlDown))
      Set findit = OutSH.Range("A:A").Find(what:=ce)
      findit.Offset(0, 1).Value = ce.Offset(0, 1).Value
      findit.Offset(0, 2).Value = Cells(5, i - 1).Value
    Next ce
  Next i
  
  
End Sub

It only works on the monthly data. From there, you could use the weeknum function to determine the week for comparison purposes.

HTH

rylo

thanks! I'll check this out