how do i duplicate rows based on a cells value

Copper Contributor

im trying to get data from one table and add it multiple times to another table based on the max qty

productmax qtyorder productqty 
product 1612 product 16 
product 2713 product 16 
product 31014 product 27 
    product 26 
    product 310 
    product 34 
5 Replies
The desired result is not clear from the example. To pull rows multiple times you could use CHOOSEROWS/SEQUENCE or INDEX/SEQUENCE.

@ajames1450 

Sub max_qty()

Dim i As Long
Dim j As Long
Dim m As Long
Dim l As Long
Dim k As Long

Range("E:F").Clear
Cells(1, 5).Value = "product"
Cells(1, 6).Value = "qty"
i = Range("A" & Rows.Count).End(xlUp).Row
k = 2

For j = 2 To i

l = Application.WorksheetFunction.RoundUp((Cells(j, 3).Value / Cells(j, 2).Value), 0)

For m = 1 To l

If m = l Then
Cells(k, 6).Value = Cells(j, 3).Value - (Cells(j, 2).Value * (m - 1))
Cells(k, 5).Value = Cells(j, 1).Value
k = k + 1

Else
Cells(k, 6).Value = Cells(j, 2).Value
Cells(k, 5).Value = Cells(j, 1).Value
k = k + 1

End If

Next m
Next j

End Sub

An alternative could be these lines of code. In the attached file you can click the button in cell H2 to run the macro.

product max qty.JPG

@ajames1450 I believe I understand what you are asking for.  In the attached spreadsheet, I have retained your posted data (in A1:E7), but clarified the heading in C1.

AJ_1.png

Note that empty rows (in the first three columns) may appear to not cause a problem, but should be avoided.  If you have too many at the wrong location, that will result in wrong calculations.

 

The formulas for row 2 (which should be copied down as needed):

'rows needed
=IFERROR( CEILING(C2/B2, 1), "-" )
'first row [into which to populate this product row]
=IF( A2="", "-", 2+SUM(F$1:F1) )
'Ref[ers to] Row
=IFERROR( MATCH( ROW(), G$2:G2, 1)+1, 2 )
'Product
=IF( J2=0, "-", INDIRECT("A"&H2) )
'Qty to [include in this] Ship[ment/Box/etc.]
=MIN( INDIRECT("B"&H2), IF(H2<>H1, INDIRECT("C"&H2), INDIRECT("C"&H2)-SUMIFS(J$1:J1,H$1:H1,H2) ) )
' (The third INDIRECT and the SUMIFS are calculating the
' unfilled part of the order at that row.)

Note that most of the formulas are sensitive to the data starting in row 2.  Row 1 should contain only text values.

 

You don't say which version of Excel you are using.  If you are using one of these versions, you can use the LET function to reduce the number of columns used (and write more understandable formulas): Excel for Microsoft 365; Excel for Microsoft 365 for Mac; Excel for the web; Excel 2021; Excel 2021 for Mac

@ajames1450 alternate solution using a complex array formula:

=LET(in, Table1,
       seq,ROUNDUP(Table1[order]/Table1[max qty],0),
  length, SUM(seq),
  cumseq, VSTACK(0,SCAN(0,seq,LAMBDA(p,i, p+i)))+1,
  out,  REDUCE("",SEQUENCE(length),
         LAMBDA(p,i,
                  LET(thisR,INDEX(in,MATCH(i, cumseq,1)),
                          VSTACK(p,
                               HSTACK(INDEX(thisR,,1),
                                              IF(ISNUMBER(MATCH(i,cumseq-1,0)),LET(a,MOD(INDEX(thisR,,3),INDEX(thisR,,2)),IF(a,a,INDEX(thisR,,2))),INDEX(thisR,,2))))))),
      DROP(out,1))

@ajames1450 

Another alternative could be Power Query.

product qty.JPG