How to divide cell value in many lines?

Alex_Barbosa99 

OliverScheurich , thanks for the idea. Tried to repeat without UI

let
    Source = Excel.CurrentWorkbook(),
    input = Source{[Name="Input"]}[Content],
    divisor = Source{[Name="divisor"]}[Content],

    getRepeats = List.Zip(
        {
            input[Column1],
            List.Transform(
                input[Column2],
                (q) => Number.Round( Number.IntegerDivide(q, divisor[Column1]{0} ) ) )
        } ),
    repeatIt = Table.FromList(
        List.Combine(
            List.Transform( getRepeats, (q) => List.Repeat( {q{0}},
            List.Max( {1, q{1} } ) ) )
        ),
        null,
        {"Output"}
    )

in
    repeatIt

on

Alex_Barbosa99 

Another possible solution with Power Query. In the attached file you can add data to the large blue dynamic table and a divisor in the small blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

Alex_Barbosa99 

Sub expand()

Dim h, i, j, k, l, m, n As Long

Range("D:E").Clear
m = 1
h = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To h

j = Cells(i, 2).Value / Range("I1").Value
k = Application.WorksheetFunction.RoundUp(j, 0)

For l = 1 To k
Cells(m, 4).Value = Cells(i, 1).Value

If Cells(i, 2).Value - n > Range("I1").Value Then
Cells(m, 5).Value = Cells(i, 2).Value / j
Else
Cells(m, 5).Value = Cells(i, 2).Value - n
End If
n = n + Range("I1").Value
m = m + 1

Next l
n = 0

Next i

End Sub

You can try these lines of code. In the attached file you can click the button in cell H2 to run the macro. The divisor is entered in cell I2 which is 120 in this example.

Alex_Barbosa99 

As variant

=LET(
    repeat, BYROW(data,
        LAMBDA(v, LAMBDA(
            LET(
                n, INT(INDEX(v, 2) / divider),
                IF(n, IF(SEQUENCE(n), v), v))
        ) )
    ),
    first, INDEX(repeat, 1, 1)(),
    rest, INDEX(repeat, SEQUENCE(ROWS(repeat) - 1, , 2), 1),
    REDUCE(first, rest, LAMBDA(a,v, VSTACK(a, v())))
)

Alex_Barbosa99 As an alternative, this can be achieved fairly easy in Power Query as well. See attached.

Alex_Barbosa99  I have been avoiding macros because of security/compatibility issues.  This could be accomplished using a formula:

=LET(in,A2:B6,base,D2,
baseStack,LAMBDA(inRow,base,LET(
      name, INDEX(inRow,1),
      num, INDEX(inRow,2),
      partial,MOD(num,base),
      nBases, QUOTIENT(num,base),
      outA,IF(nBases,VSTACK("",HSTACK(TEXTSPLIT(REPT(name&";",nBases),,";",1),SEQUENCE(nBases,,base,0))),""),
      DROP(IF(partial,VSTACK(outA,HSTACK(name,partial)),outA),1))),
DROP(REDUCE("",SEQUENCE(ROWS(in)),LAMBDA(p,q,VSTACK(p,baseStack(INDEX(in,q,0),base)))),1))

someone else may even give a more simple formula but this could also be put into a lambda in the name manager if desired.