Aligning time series with different start dates

nflami

New Member
Joined
Apr 16, 2014
Messages
25
Hello all,

I am struggling with aligning multiple sets of data with different start dates that I would like to compare so I can see the trends. I have thousands of rows of data, so it not a simple cut/paste to line everything up, I'm hoping to accomplish my goal with a few formulas.

To start simple I have two rows of data. Each with different "start dates". One of Feb-14, the other of Apr-14, with monthly sales of each

Start DateJan-14Feb-14Mar-14Apr-14May-14Jun-14Jul-14Aug-14Sep-14Oct-14Nov-14Dec-14
Feb-140101010100000000
Apr-14000151515000000

For the above table I would like the start date to be period 1 for each row, and go forward from that point. So the first line period 1 would be Feb-14 and second line period 1 would be Apr-14. In other words offset Apr-14 to the left 2 columns.

I have tried to use subtraction of the date across the top row and the start date to assign periods and then use vlookup to replace the values with sales, but that still leaves everything offset further the opposite way. For instance Feb14 minus Feb14 equals 0, and Mar14 minus Feb14 equals 1. In the formula I added +1 to each to make the 0 my period 1. Same with the other row, Apr14 minus Apr14 equals 0, May14 minus Apr14 equals 1. This way I had my start dates to all equal period 1, but I didn't know where to go from there to align all the ones.

This is my end goal:
Period123456789101112
Feb-141010101000000000
Apr-14151515000000000

Please let me know if you have any questions.

Thanks in advance!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Code:
Option Explicit

Sub AlignStartMonth()

    Dim dteStart As Date
    Dim lLastRow As Long
    Dim lLastCol As Long
    Dim lRowIndex As Long
    Dim varMatch As Variant
    Dim rngMatch As Range
    
    lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
    lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

    Set rngMatch = Range(Cells(1, 1), Cells(1, lLastCol))

    For lRowIndex = 2 To lLastRow
        If Cells(lRowIndex, lLastCol + 1) = vbNullString Then  'To prevent processing a row twice
            dteStart = Cells(lRowIndex, 1)
            On Error Resume Next 'If date not present Match raises an error
            varMatch = Application.WorksheetFunction.Match(dteStart, rngMatch.Value, 0)
            If Err.Number = 0 Then
                If Not IsEmpty(varMatch) Then
                    'Found Date
                    Range(Cells(lRowIndex, 2), Cells(lRowIndex, varMatch - 1)).Delete shift:=xlShiftToLeft
                    Cells(lRowIndex, lLastCol + 1) = "Converted"
                End If
            Else
                Cells(lRowIndex, lLastCol + 1) = "Could Not Find Date Match"
            End If
            On Error GoTo 0
        End If
    Next
    
    Set rngMatch = Nothing
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top