Complicated sum
I lited all the changes I made and then the new code
Changes to fix columns
from
.Range(NewCol & RowCount).Offset(0, 3) = NewProduct
.Range(NewCol & RowCount).Offset(0, 4) = Order - 400
to
.Range(NewCol & RowCount).Offset(0, 2) = NewProduct
.Range(NewCol & RowCount).Offset(0, 3) = Order - 400
Change to fix combining orders
from
If Order <= 400 Then
If Order + Quant <= 400 Then
OldOrder(OldItem) = Order
Else
to
If Order + Quant <= 400 Then
If Order <= 400 Then
OldOrder(OldItem) = Order
Else
To make last row multiple of 400 I used two bobines instead of 3 (100,
3600).
Also see if you like the result in this case
A 150
A 250 A 3200
A 500
from
.Range(NewCol & RowCount) = NewProduct
.Range(NewCol & RowCount).Offset(0, 1) = 400
.Range(NewCol & RowCount).Offset(0, 3) = NewProduct
.Range(NewCol & RowCount).Offset(0, 4) = Order - 400
to
Remainder = Order Mod 400
.Range(NewCol & RowCount) = NewProduct
.Range(NewCol & RowCount).Offset(0, 1) = Remainder
.Range(NewCol & RowCount).Offset(0, 2) = NewProduct
.Range(NewCol & RowCount).Offset(0, 3) = Order - Remainder
New Code
Sub CombineOrders()
Dim OldProduct(1 To 2)
Dim OldOrder(1 To 2)
'arrays fill with in the following order
'1 = Col A and Col B data
'2 = Col C and Col D
'3 = Next Row Col A and Col B
'4 = Next Row Col C and Col D
Dim NextProduct(1 To 4)
Dim NextOrder(1 To 4)
With Sheets("bobines")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To 4
NextProduct(i) = ""
NextOrder(i) = 0
Next i
For i = 1 To 2
OldOrder(i) = 0
OldProduct(i) = ""
Next i
For RowCount = 2 To LastRow
NextProduct(1) = .Range("A" & RowCount)
NextOrder(1) = .Range("B" & RowCount)
NextProduct(2) = .Range("C" & RowCount)
NextOrder(2) = .Range("D" & RowCount)
NextProduct(3) = .Range("A" & (RowCount + 1))
NextOrder(3) = .Range("B" & (RowCount + 1))
NextProduct(4) = .Range("C" & (RowCount + 1))
NextOrder(4) = .Range("D" & (RowCount + 1))
'loop twice, one for column A-B and then C-D
For Item = 1 To 2
If .Range("E" & RowCount) = "" Then
NewCol = "E"
Else
NewCol = "G"
End If
If Item = 1 Then
NewProduct = .Range("A" & RowCount)
NewOrder = .Range("B" & RowCount)
Else
NewProduct = .Range("C" & RowCount)
NewOrder = .Range("D" & RowCount)
End If
If NewProduct < "" Then
'see if new product matches one of products on bobines
If NewProduct = OldProduct(1) Then
OldItem = 1
Else
If NewProduct = OldProduct(2) Then
OldItem = 2
Else
'does not match, see which bobine is empty
If OldProduct(1) = "" Then
OldItem = 1
OldProduct(OldItem) = NewProduct
Else
If OldProduct(2) = "" Then
OldItem = 2
OldProduct(OldItem) = NewProduct
Else
'2nd bobine should be empty, if not error
Stop
End If
End If
End If
End If
Order = OldOrder(OldItem) + NewOrder
Found = False
For CompareItem = (Item + 1) To 4 'don't compare against itself
If NextProduct(Item) = NextProduct(CompareItem) Then
NextItem = CompareItem
Found = True
Exit For
End If
Next CompareItem
If Found = True Then
'product matches
Quant = NextOrder(NextItem)
If Order + Quant <= 400 Then
If Order <= 400 Then
OldOrder(OldItem) = Order
Else
.Range(NewCol & RowCount) = NewProduct
.Range(NewCol & RowCount).Offset(0, 1) = Order
OldProduct(OldItem) = ""
OldOrder(OldItem) = 0
End If
Else
OldOrder(OldItem) = Order
End If
Else
'Product doesn't match put on bobbines
If Order <= 400 Then
.Range(NewCol & RowCount) = NewProduct
.Range(NewCol & RowCount).Offset(0, 1) = Order
Else
Remainder = Order Mod 400
.Range(NewCol & RowCount) = NewProduct
.Range(NewCol & RowCount).Offset(0, 1) = Remainder
.Range(NewCol & RowCount).Offset(0, 2) = NewProduct
.Range(NewCol & RowCount).Offset(0, 3) = Order - Remainder
End If
OldProduct(OldItem) = ""
OldOrder(OldItem) = 0
End If
End If
Next Item
Next RowCount
End With
End Sub
"LiAD" wrote:
Hot stuff. Love it!
Its super close but there are just a few little things Ive noticed.
Grouping doesnt seem to work in cases such as the following
Inputs ------ Results given -------
A 150 A 3200 A 150
A 250 A 400 A 3050
In this case the formula should give two only A's, 400 and 3200.
If I try the same inputs but in a different order I get a different result
Inputs ------------- Results given---------
A 150
A 250 A 3200 A 400 A 400 A 2800
- Is it possible to have the same outputs in the two cases above?
- Is it possible to assure that the last output is always a multiple of 400,
so for example if we replaced the 3200 with 3300 the outputs would be
400,100,3200?
- Is there a way of ensuring the position of the results is constant?
In the example below it puts the last result for C one space to the right,
is it possible to line up this result with the others?
A 50
A 50 A 200 A 300
A 200 C 100 A 200 C 100
B 300 B 300
B 175 B 300 B 175
B 55 C 500 B 355
C 125 C 400 C 225
The last C in the bottom right should be below the C four cells above (col
G) rather than the 100 (col H).
Thanks a million for your help
LiAD
-------------------------------------------
"Joel" wrote:
Try this. Should work in both cases
Sub CombineOrders()
Dim OldProduct(1 To 2)
Dim OldOrder(1 To 2)
'arrays fill with in the following order
'1 = Col A and Col B data
'2 = Col C and Col D
'3 = Next Row Col A and Col B
'4 = Next Row Col C and Col D
Dim NextProduct(1 To 4)
Dim NextOrder(1 To 4)
With Sheets("bobines")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To 4
NextProduct(i) = ""
NextOrder(i) = 0
Next i
For i = 1 To 2
OldOrder(i) = 0
OldProduct(i) = ""
Next i
For RowCount = 2 To LastRow
NextProduct(1) = .Range("A" & RowCount)
NextOrder(1) = .Range("B" & RowCount)
NextProduct(2) = .Range("C" & RowCount)
NextOrder(2) = .Range("D" & RowCount)
NextProduct(3) = .Range("A" & (RowCount + 1))
NextOrder(3) = .Range("B" & (RowCount + 1))
NextProduct(4) = .Range("C" & (RowCount + 1))
NextOrder(4) = .Range("D" & (RowCount + 1))
'loop twice, one for column A-B and then C-D
For Item = 1 To 2
If .Range("E" & RowCount) = "" Then
NewCol = "E"
Else
NewCol = "G"
End If
If Item = 1 Then
NewProduct = .Range("A" & RowCount)
NewOrder = .Range("B" & RowCount)
Else
NewProduct = .Range("C" & RowCount)
NewOrder = .Range("D" & RowCount)
End If
If NewProduct < "" Then
'see if new product matches one of products on bobines
If NewProduct = OldProduct(1) Then
OldItem = 1
Else
If NewProduct = OldProduct(2) Then
OldItem = 2
Else
'does not match, see which bobine is empty
If OldProduct(1) = "" Then
OldItem = 1
OldProduct(OldItem) = NewProduct
Else
If OldProduct(2) = "" Then
OldItem = 2
OldProduct(OldItem) = NewProduct
Else
'2nd bobine should be empty, if not error
Stop
End If
End If
End If
End If
Order = OldOrder(OldItem) + NewOrder
Found = False
For CompareItem = (Item + 1) To 4 'don't compare against itself
If NextProduct(Item) = NextProduct(CompareItem) Then
NextItem = CompareItem
Found = True
Exit For
End If
Next CompareItem
If Found = True Then
'product matches
Quant = NextOrder(NextItem)
If Order <= 400 Then
If Order + Quant <= 400 Then
OldOrder(OldItem) = Order
Else
.Range(NewCol & RowCount) = NewProduct
.Range(NewCol & RowCount).Offset(0, 1) = Order
OldProduct(OldItem) = ""
OldOrder(OldItem) = 0
End If
Else
OldOrder(OldItem) = Order
End If
Else
'Product doesn't match put on bobbines
If Order <= 400 Then
.Range(NewCol & RowCount) = NewProduct
.Range(NewCol & RowCount).Offset(0, 1) = Order
Else
.Range(NewCol & RowCount) = NewProduct
.Range(NewCol & RowCount).Offset(0, 1) = 400
.Range(NewCol & RowCount).Offset(0, 3) = NewProduct
.Range(NewCol & RowCount).Offset(0, 4) = Order - 400
End If
OldProduct(OldItem) = ""
OldOrder(OldItem) = 0
End If
End If
Next Item
Next RowCount
End With
End Sub
"LiAD" wrote:
Hi,
Thanks a lot for your help. Relief to find that maybe a way is possible!
One thing I've noticed is that this code stops after 2 rows. So the inputs
and result for the following is
A 50
A 50 100
A 200 200
whereas the desired output is
A 50
A 50
A 200 300
I guess its better to know now rather than later.
Thanks a lot for your help again
LiAD
"Joel" wrote:
the code below give the first table results. the code is checking only
column A to see if products match. I have to look at the 2nd table. will
get back to you later
Sub CombineOrders()
With Sheets("bobines")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
PreviousOrder = 0
For RowCount = 2 To LastRow
Order = .Range("B" & RowCount)
Order = Order + PreviousOrder
If PreviousOrder = 0 Then
If .Range("A" & RowCount) = .Range("A" & (RowCount + 1)) Then
'product matches
NextOrder = .Range("B" & (RowCount + 1))
If Order <= 400 Then
If Order + NextOrder <= 400 Then
PreviousOrder = Order
Else
.Range("C" & RowCount) = Order
End If
Else
PreviousOrder = Order
End If
Else
'Product doesn't match put on bobbines
If Order <= 400 Then
.Range("C" & RowCount) = Order
Else
.Range("C" & RowCount) = 400
.Range("D" & RowCount) = Order - 400
End If
PreviousOrder = 0
End If
Else
'previous order doesn't match don't not combine 3 rows
If Order <= 400 Then
.Range("C" & RowCount) = Order
Else
.Range("C" & RowCount) = 400
.Range("D" & RowCount) = Order - 400
End If
PreviousOrder = 0
End If
Next RowCount
End With
End Sub
"LiAD" wrote:
Yeah I know Im too close. Ill try more simply.
Copper wire is made in certain diameters, 0.2mm, 0.3mm, 0.4mm etc. In order
to transport the wire is wound onto bobines. Each bobine size has a maximum
weight that it can support €“ 400kg in this case.
Imagine a two stage process where u make the wire xx diameter then u send it
to another machine to get the plastic put around the outside. The output
from the plastic wrapping machine will also be wound onto bobines.
Customer A would like 200kg, B 100kg, C 50kg all of exactly the same wire,
0.3mm with a red plastic coat. On machine 1 to save time, bobines, space etc
we will make one big bobine of 350kg of 0.3mm wire to send the wrapping
machine. The wire will be wrapped in red plastic and wound onto three
separate bobines (200,100,50kg). If we dont combine the orders on the first
machine we need more bobines, more stopping/starting of the machine, more
set-ups, more time etc etc. By combining the customer gets what they need
and we reduce our timescale.
Life not being so easy they dont all want the same thing of course. They
want different diameters, colours and compositions. Compositions???? Some
customers dont want one big wire of 0.2mm diameter, they want two wires a
smaller diameter stuck together to make one of 0.2mm. So on machine 1
instead of making 200kg of 0.2mm it needs to make 150kg of 0.07mm + 50kg of
0.05mm or whatever is needed.
The product code I referred to in the last post (A,B,C) can be thought of as
wire diameter. What I would like is a method of excel deciding which bobines
can be grouped and which cannot. In order to be grouped they must have the
same diameter AND be sequential on the list. If they cant be grouped, too
bad for us, we make it on smaller bobines.
If one customer wants 600kg in any case he will be forced to take two
bobines, one of 400kg and one of 200kg. If however the next customer wants
the same diameter for 100kg we can combine that with the second bobine of
200kg we will make for the first customer so the factory would make 400kg and
a 300kg bobine on machine 1. If however one customer wanted 390kg and
another 50kg we do not want to make one 400kg and one of 40kg as the first
customer can have his 390 but the second will have a 10 with a 40 joined to
it €“ not very useful in cables. Third case is if the first customer wants
more than 420kg and the second 50kg we do not want to combine the orders as
this would create a very small second bobine of 70kg. In this case we need
do something special to make a 420kg and a 50kg €“ costly and time consuming
but thats life.
|