Complicated sum
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.
So the logic is
- test if diameters (A,B,C etc) are the same from cols A and C
- if false return the original bobine weight
- if true add the bobine weights and test if =<400,
o if true add the next bobine weight, retest to see if the cumulative wght
=<400 and this continues until it hits the limit
when it hits the limit it writes the bobine weights to be produced
opposite the last item,
o if false test to see if all the bobines are between 100 and 400kg.
if no more additions possible divides the bobines weights as required by
the grouping and customers opposite the last item.
if can add more continue until it hits the limit then write the individual
bobine weights opposite the last item
Does that (quite long, maybe too long) description make it any easier?
"Patrick Molloy" wrote:
I can't get my head around this I'm afraid.
We need an algorithm. That is, a sequence of logical steps ...
You're very close to this project, so it woudl help maybe if you took a step
back and started as if we're total beginners - which we are ;)
thanks
"LiAD" wrote:
Morning,
I have a problem which I have tried to post on the functions forum but no
answer. Its difficult to explain so Im not sure if people are understanding
what Im after.
In order to make copper wire it is wound onto bobines which can support a
maximum weight of 400kg. The machine that makes the bobines is very fast so
if it can avoided its better not to make bobines smaller than 100kg, however
if we must we can. If a customer needs 600kg of wire they will get a bobine
of 400kg and another of 200kg. To make it more difficult each product can be
based on several sub compositions. First of all Ill explain it for the
products made from single components if thats not understood then the full
solution will be completely lost.
Single components-
What I would like is a formula/macro that can look through the data and
group the products that are the same. In col A of the sheet named (Bobines)
I have my product list (A,B,C) in col B I have the individual weights of the
bobines required. In col C,D,E I would like to generate the results the
individual weights of the bobines to be produced. The sequence is
match the products, sum the weights then if:
- the sum is less than 400kg continue to add weights until the limit of 400
is reached
- the sum (or a component) is over 400kg continue adding until the orders
can be split into 100kg< bobine weight < 400kg.
- if no sum is possible just write the weight of the bobine as per the
original customer order.
- Where weights are being added to the next bobine just write nothing (blank
or ) as a result
- write the cumulative weights in the cell adjacent to the
last bobine that was added
(Just for ref these bobines are not actually sold then are then fed into
other processes so they will be resplit into the original orders further down
the line after additional work).
Results
Product Customer Wt Bob 1 Bob 2
B 390 390
B 90 90
C 400 400
C 90 90
A 420 ---
A 90 400 110
D 600 ----
D 90 400 290
B 50 ----
B 100 150
Multiple components
The added difficulty here is that the formula/code needs to look in several
places to produce the result as it can match with the product in col A OR col
C AND continue as long vertically as it wants until it reaches the limits of
100kg < xx < 400kg. In total there are four positions in which to match
between consecutive rows (A-A, A-C, C-A and C-C).
Results
A 100 --- --- --- ---
A 100 --- --- --- ---
A 50 A 75 --- --- A 425
B 150 B 55 --- --- --- ---
B 175 C 65 B 380 --- ---
B 55 C 75 B 55 --- ---
C 125 D 85 C 200 D 85
B 20 A 50 B 20 A 50
In both cases only items that are adjacent on the list should be grouped
irrespective of weights, (hence why the last group of A&Bs are not grouped
with the first lot).
It is the second case of multiple components that I need to get to as a
working result.
Is this iterative procedure possible?
I guess a macro is the easiest way to go otherwise itll be a never ending
list of IF formulas.
Thanks
LiAD
|