# Aligning time series with different start dates

#### nflami

##### New Member
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 Date Jan-14 Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Feb-14 0 10 10 10 10 0 0 0 0 0 0 0 Apr-14 0 0 0 15 15 15 0 0 0 0 0 0

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:
 Period 1 2 3 4 5 6 7 8 9 10 11 12 Feb-14 10 10 10 10 0 0 0 0 0 0 0 0 Apr-14 15 15 15 0 0 0 0 0 0 0 0 0

Please let me know if you have any questions.

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### pbornemeier

##### Well-known Member
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``````

Replies
5
Views
135
Replies
4
Views
144
Replies
23
Views
814
Replies
1
Views
260
Replies
2
Views
247

1,195,963
Messages
6,012,589
Members
441,714
Latest member
mcgeesusana

### 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.

### Which adblocker are you using?

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

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