Expand start date to end date with EOMONTH series
I have a data table containing ids with a start date and an 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 this table to allow one row per emond between start and end date (inclusive) for each AcNo. Line numbers are irrelevant.
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 make each line with a couple 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 lines of AcNos and this is awkward to do for individual lines.
I also used VBA DateDiff to create a loop for individual lines.
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 line at a time.
How would I loop through the rows, stacking each series into one column? Any suggestions for adjusting my formula or code would be appreciated.
source to share
Just because you seem to be asking for multiple options, here's one without VBA:
- Expand the table on the right with a formula (using structured links here):
=IF(EOMONTH(Table1[@[StartDate]:[StartDate]],COLUMNS($A:A))<Table1[@[EndDate]:[EndDate]],EOMONTH(Table1[@[StartDate]:[StartDate]],COLUMNS($A:A)),"")
- Use
Power Query
orData Get & Transform
to unPivot all but the first two columns: (easy to do in the GUI, but I am pasting below code 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"
- Sorting results by AcNo, then by date:
Note that when this is done the first date is actually the date of the spec, but if you format them the same way as in the results, mmm yy, it looks the same. And it's easy to change if that's a problem.
If the first month is not required as the BOM date:
- Change the formula to:
=IF(EOMONTH(Table1[@[StartDate]:[StartDate]],COLUMNS($A:A)-1)<=Table1[@[EndDate]:[EndDate]],EOMONTH(Table1[@[StartDate]:[StartDate]],COLUMNS($A:A)-1),"")
- On execution,
Data Get & Transform
delete the columnStartDate
in the Query editor, as this will not affect other columns at the time.
source to share
Try this as a nested For ... Next loop using DateDiff to find the number of months. Collecting progressive values in an array will speed up execution before dumping them back into 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 DateSerial can be used as an EOMONTH generator by setting the day to zero for the next month.
Notice in the following image that the months generated are the EOMONTH of each month in the series, formatted with the cell number mmm yy.
source to share