Expand Start Date to End Date with Series of EOMON

问题:

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.

回答1:

Only because you seem to be soliciting multiple options, here is one without VBA:

• Expand your table to the right with a formula (using structured references here):

---

=IF(EOMONTH(Table1[@[StartDate]:[StartDate]],COLUMNS($A:A))<Table1[@[EndDate]:[EndDate]],EOMONTH(Table1[@[StartDate]:[StartDate]],COLUMNS($A:A)),"")

• Use Power Query or Data Get & Transform to unPivot all except the first two columns: (easily done in the GUI, but I paste the code below for interest)

---

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RowNo", Int64.Type}, {"AcNo", type text}, {"StartDate", type datetime}, {"EndDate", type datetime}, {"Column1", type datetime}, {"Column2", type datetime}, {"Column3", type datetime}, {"Column4", type datetime}, {"Column5", type datetime}, {"Column6", type datetime}, {"Column7", type datetime}, {"Column8", type datetime}, {"Column9", type datetime}, {"Column10", type datetime}, {"Column11", type datetime}, {"Column12", type datetime}, {"Column13", type datetime}, {"Column14", type datetime}, {"Column15", type datetime}, {"Column16", type datetime}, {"Column17", type datetime}, {"Column18", type datetime}, {"Column19", type datetime}, {"Column20", type datetime}, {"Column21", type datetime}, {"Column22", type datetime}, {"Column23", type datetime}, {"Column24", type datetime}, {"Column25", type datetime}, {"Column26", type datetime}, {"Column27", type datetime}, {"Column28", type datetime}, {"Column29", type datetime}, {"Column30", type datetime}, {"Column31", type datetime}, {"Column32", type datetime}, {"Column33", type datetime}, {"Column34", type datetime}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"RowNo", "AcNo"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "EOM Date"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"EOM Date", type date}})
in
    #"Changed Type1"

• Sort the results by AcNo and then by Date:

Note that, when done this way, the first date is actually a BOM date, but if you format them as in your results, mmm yy, it'll look the same. And things are easily changed if that is an issue.

If having the first month as a BOM date is not desired:

• Change the formula to:

---

=IF(EOMONTH(Table1[@[StartDate]:[StartDate]],COLUMNS($A:A)-1)<=Table1[@[EndDate]:[EndDate]],EOMONTH(Table1[@[StartDate]:[StartDate]],COLUMNS($A:A)-1),"")

---

• When executing the Data Get & Transform, delete the StartDate column in the Query GUI editor, as this will not affect the other columns at that time.

回答2:

Try it as a nested For ... Next loop using DateDiff to determine the number of months. Collecting the progressive values in an array will speed up execution before dumping them back to the worksheet.

Option Explicit

Sub eoms()
    Dim a As Long, m As Long, ms As Long, vals As Variant
    With Worksheets("Sheet2")
        .Range("F1:G1") = Array("AcNo", "EOMONTHs")
        For a = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
            ms = DateDiff("m", .Cells(a, "C").Value2, .Cells(a, "D").Value2)
            ReDim vals(1 To ms + 1, 1 To 2)
            For m = 1 To ms + 1
                vals(m, 1) = .Cells(a, "B").Value2
                vals(m, 2) = DateSerial(Year(.Cells(a, "C").Value2), _
                                        Month(.Cells(a, "C").Value2) + m, _
                                        0)
            Next m
            .Cells(.Rows.Count, "F").End(xlUp).Offset(1, 0).Resize(UBound(vals, 1), UBound(vals, 2)) = vals
        Next a
        .Range(.Cells(2, "G"), .Cells(.Rows.Count, "G").End(xlUp)).NumberFormat = "mmm yy"
    End With
End Sub

VBA's DateSerial can be used as a EOMONTH generator by setting the day to zero of the following month.

Note in the following image that the generated months are the EOMONTH of each month in the series with mmm yy cell number formatting.