I have the following test dataset:

And I want to create the following with Excel or VBA: enrich the dataset with the upcoming periods (like a forecast) until the End date with the same costs. It doesn't have to be in a specific chronological order but the output should be something like this:

How do I get this done in Excel? Any suggestions?

CodePudding user response：

Try:

Formula in F1:

=LET(a,DATEDIF(B2:B4,D2:D4,"m"),b,SCAN(0,a,LAMBDA(X,Y,X Y)),c,SEQUENCE(SUM(a)),d,INDEX(A2:D4,XMATCH(c,b,1),{1,2,3,4}),e,SCAN(EOMONTH(B2,-1),c,LAMBDA(f,g,IF(INDEX(d,MAX(g-1,1),1)=INDEX(d,g,1),EOMONTH(f,0) 1,INDEX(d,g,2)))),VSTACK(A1:D1,CHOOSECOLS(HSTACK(d,e),{1,5,3,4})))

CodePudding user response：

Please, try the next code. It creates an array of months interval, calculated by difference between the End Date and Period, then place it in the right position of a newly created sheet ("Forecast"). If this sheet already exists (second time when run the code), it is only cleared. The code will process all products existing in A:A column. It should work on all Excel versions:

Sub MakeForecast()
  Dim sh As Worksheet, shF As Worksheet, lastR As Long, arrHead, arr, arrD, i As Long
  
  Set sh = ActiveSheet
  If sh.name = "Forecast" Then MsgBox "You activated a wrong sheet...": End Sub 'for the case of activating it by mistake
  lastR = sh.Range("A" & sh.rows.count).End(xlUp).row
  arrHead = sh.Range("A1:D1").Value
  arr = sh.Range("A2:D" & lastR).Value2
  
  On Error Resume Next
    Set shF = Worksheets("Forecast") 'check if "Forecast" sheet is already created
  On Error GoTo 0
  If shF Is Nothing Then   'if not, create it and name "Forecast"
        Set shF = Worksheets.Add(After:=sh)
        shF.name = "Forecast"
  Else
        shF.cells.Clear    'if created, clear all its cells contents
  End If
  For i = 1 To UBound(arr) 'iterate between the array rows
        arrD = makeMonthsDateRange(CDate(arr(i, 2)), CDate(arr(i, 4))) 'use the function to create the moths array
        createProdRange shF, arrD, CStr(arr(i, 1)), CDbl(arr(i, 3)), CDate(arr(i, 4)), IIf(i = 1, arrHead, Array("")) 'drop the array content and format a little
  Next i
  shF.Columns("A:D").EntireColumn.AutoFit
  MsgBox "Ready..."
End Sub

Private Function makeMonthsDateRange(frstD As Date, lastD As Date) As Variant
     Dim countD As Long, arrM
     countD = DateDiff("m", frstD, lastD, vbMonday)
     makeMonthsDateRange = Evaluate("DATE(" & Year(frstD) & ",ROW(1:" & countD & "),1)")
End Function

Sub createProdRange(ws As Worksheet, arrMonths, strProduct As String, cost As Double, endDate As Date, arrH)
   If UBound(arrH) > 0 Then ws.Range("A1").Resize(1, UBound(arrH, 2)).Value = arrH
    Dim lastR As Long: lastR = ws.Range("A" & ws.rows.count).End(xlUp).row   1
    With ws.Range("A" & lastR, "D" & UBound(arrMonths)   lastR - 1)
        .Columns(1).Value = strProduct
         With .Columns(2)
            .Value2 = arrMonths
            .NumberFormat = "dd-mm-yyy"
         End With
         .Columns(3).Value2 = cost
         With .Columns(4)
            .Value2 = endDate
            .NumberFormat = "dd-mm-yyy"
         End With
    End With
End Sub

Please, send some feedback after testing it. If something not clear enough, please do not hesitate to ask for clarifications.