Oct 28 2022 01:32 PM
im trying to get data from one table and add it multiple times to another table based on the max qty
product | max qty | order | product | qty | ||
product 1 | 6 | 12 | product 1 | 6 | ||
product 2 | 7 | 13 | product 1 | 6 | ||
product 3 | 10 | 14 | product 2 | 7 | ||
product 2 | 6 | |||||
product 3 | 10 | |||||
product 3 | 4 |
Oct 28 2022 01:40 PM
Oct 28 2022 05:26 PM
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.
Oct 28 2022 05:57 PM
@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.
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
Oct 28 2022 06:36 PM
@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))
Oct 29 2022 03:03 AM