Expand Start Date to End Date with Series of EOMONTHs

Viewed 343

I have a data table containing ids with a start date and end date associated with both.

RowNo   AcNo     StartDate     EndDate
  1     R125     01/10/2017    30/09/2020
  2     R126     01/10/2017    30/09/2018
  3     R127     01/10/2017    30/09/2019
  4     R128     01/10/2017    30/09/2020

I need to expand (i.e. unpivot) this table to allow one row for each eomonth between the start and end date (inclusive) for each AcNo. The row numbers are unimportant.

AcNo    EOMONTHs
R125    Oct 17
R125    Nov 17
R125    Dec 17
R125    Jan 18
R125    Feb 18
R125    Mar 18
    ...
R128    Apr 20
R128    May 20
R128    Jun 20
R128    Jul 20
R128    Aug 20
R128    Sep 20

I can do each row with a pair of formulas like this,

'in F2
=IF(ROW(1:1)-1<DATEDIF(C$2, D$2, "m"), B$2, TEXT(,))
'in G2
=IF(ROW(1:1)-1<DATEDIF(C$2, D$2, "m"), EOMONTH(C$2, ROW(1:1)-1), TEXT(,))
'F2:G2 filled down

However I have thousands of rows of AcNos and this is unwieldy to perform for individual rows.

I've also used VBA's DateDiff to form a loop for individual rows.

    Dim m As Long, ms As Long
    With Worksheets("Sheet2")
        .Range("F1:G1") = Array("AcNo", "EOMONTHs")
        ms = DateDiff("m", .Cells(2, "C").Value2, .Cells(2, "D").Value2)
        For m = 1 To ms + 1
            .Cells(m, "M") = .Cells(2, "B").Value2
            .Cells(m, "N").Formula = "=EOMONTH(C$2, " & m - 1 & ")"
        Next m
    End With

Again this only expands one row at a time.

How would I loop through the rows stacking each series into a single column? Any suggestions for adjustments to my formula or code would be welcome.

2 Answers
Related