Creating rows automatically based on value limit in Microsoft Excel

Question

I want to create a carton based on maximum unit per carton for particular item.

Eg.

Item    Quantity    MaxQtyPerCarton
A          12            5
B           6            3

This should give results as follows by creating rows in Excel

Item    CartonQuantity
A          5
A          5
A          2
B          3
B          3

You can see the item A quantity has been divided into three rows based on MaxQtyPerCarton. Also, Item B has been divided into two rows based on MaxQtyPerCarton.

Any idea on this?

Sorry, on my search i didn't see this question being asked before that's why i posted here. Is this still valid to get an answer? — emnzava, Jun 27 '14 at 14:54

score 1 · Accepted Answer · answered Jun 27 '14 at 16:08

The VBA approach (just programming it)

Option Explicit

Sub Sub1()
  Dim iRow1&, iRow2&, zItem$, zQuan&, zMaxQ&, zAmt&
  iRow2 = 10 ' ??
  For iRow1 = 2 To 3 ' ??
     zItem = Cells(iRow1, 1)
     zQuan = Cells(iRow1, 2)
     zMaxQ = Cells(iRow1, 3)
     Do While zQuan > 0
       zAmt = zQuan
       If zAmt > zMaxQ Then zAmt = zMaxQ
       Cells(iRow2, 1) = zItem
       Cells(iRow2, 2) = zAmt
       iRow2 = iRow2 + 1
       zQuan = zQuan - zAmt
     Loop
  Next iRow1
End Sub

score 0 · Answer 2 · edited Jun 27 '14 at 17:35

Assuming the table you give is in A1:C3 (with headers in row 1), enter this array formula** in E2:

=IFERROR(INDEX($A$2:$A$3,MATCH(TRUE,MMULT(0+(ROW($A$2:$A$3)>=TRANSPOSE(ROW($A$2:$A$3))),CEILING($B$2:$B$3/$C$2:$C$3,1))>=ROWS($1:1),0)),"")

Copy down until you start to get blanks for the results.

Then, this (non-array) formula in F2:

=IF(E2="","",MIN(INDEX($C$2:$C$3,MATCH(E2,$A$2:$A$3,0)),INDEX($B$2:$B$3,MATCH(E2,$A$2:$A$3,0))-INDEX($C$2:$C$3,MATCH(E2,$A$2:$A$3,0))*(COUNTIF($E$2:$E2,E2)-1)))

Copy down as required.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

score 0 · Answer 3 · answered Jun 27 '14 at 16:17

This is not super elegant, but it gets the job done. It requires that your first table structure (including headers) in your question is in range("A1:C3"). And it outputs to columns E and F.

Option Explicit

Sub FillCartons()
    Dim cll As Range
    Dim rng_Items As Range
    Dim rng_Quantity As Range
    Dim rng_MaxQty As Range
    Dim l_CartonCount As Long
    Dim l_AlreadyInCartons As Long

    Set rng_Items = Range(Range("A2"), Range("A1000000").End(xlUp))
    l_CartonCount = 1
    Range("E:F").ClearContents

    For Each cll In rng_Items
        Set rng_Quantity = cll.Offset(, 1)
        Set rng_MaxQty = cll.Offset(, 2)
        l_AlreadyInCartons = Application.WorksheetFunction.SumIf(Range("E:E"), cll.Value, Range("F:F"))

        Do Until l_AlreadyInCartons = rng_Quantity.Value
            If rng_Quantity.Value - l_AlreadyInCartons > rng_MaxQty.Value Then
                Cells(l_CartonCount, 5).Value = cll.Value
                Cells(l_CartonCount, 6).Value = rng_MaxQty.Value
            Else
                Cells(l_CartonCount, 5).Value = cll.Value
                Cells(l_CartonCount, 6).Value = rng_Quantity.Value - l_AlreadyInCartons
            End If
            l_CartonCount = l_CartonCount + 1
            l_AlreadyInCartons = Application.WorksheetFunction.SumIf(Range("E:E"), cll.Value, Range("F:F"))
        Loop
    Next cll
End Sub

score 0 · Answer 4 · answered Jun 27 '14 at 16:30

This is assuming you have following setup and you want output starting A10

enter image description here

I used Combination of Mod and Division , Division for Repetition and Mod for remainders

Sub create_rows()

Dim arr()
arr = Range("A2:C5")
Range("A10").Select

Dim j
    For j = LBound(arr, 1) To UBound(arr, 1)               

          Dim looper, z
          looper = arr(j, 2) / arr(j, 3) 'No of times to print
          Modder = arr(j, 2) Mod arr(j, 3) 'Leftovers


           For z = 1 To looper
            ActiveCell = arr(j, 1) 'Name of the quantity
            ActiveCell.Offset(0, 1) = arr(j, 3) 'always the max per qtn number
            ActiveCell.Offset(1, 0).Select
           Next z

           If (Modder > 0) Then 'there is leftover quantity 
            ActiveCell = arr(j, 1)
            ActiveCell.Offset(0, 1).Value = arr(j, 2) - arr(j, 3) * Round(looper, 0)
            ActiveCell.Offset(1, 0).Select
           End If

        Next j        

End Sub

Creating rows automatically based on value limit in Microsoft Excel

4 Answers4