Complicated sum

[LiAD]

Morning,

I have a problem which I have tried to post on the functions forum but no
answer. Its difficult to explain so I’m not sure if people are understanding
what I’m 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 I’ll explain it for the
products made from single components – if that’s 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&B’s 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 it’ll be a never ending
list of IF formulas.

Thanks
LiAD

 

[Patrick Molloy]

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

 

[Joel]

This problem is very simple once you understand the algorithm. There is an
implied requirement to use the least number of number of bobines. So you
want to to take the weight and divide by the maximum weight. This gives you
only three diffferent cases

Let W = Weight of order

1) The weight is less than the minimum wieght.
Solution: 1 bobine of the ordered weight W.
2) You divide the weight by the max weight and analize the remainder. If
the remainder is greater than the minimum you have your solution
Solution: Number of bobines is N = Int(W/400) + 1
Int is the integer part of the division.
You will have N bobines of 400 and
1 bobine of weight mod(W,400) where mod is the remainder
of the
division W/400

3) The same as case 2 except the remainder is less than the minimum. In
this case you take 100 off the last roll and then add mod(W,400)
Solution : Number of bobines is N = Int(W/400) + 1
You will have N-2 bobines of 400
You will have 1 bobine of 400 - 100 = 300
You will have 1 bobine of 100 + mod(W,400)

 

[LiAD]

Yeah I know I’m too close. I’ll 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 don’t 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 don’t all want the same thing of course. They
want different diameters, colours and compositions. Compositions???? Some
customers don’t 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 that’s 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?

 

[Joel]

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]

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]

I didn't know if you could combine multiple rows. I removed one section of
the code. Can I make the code for the multicomponent as the single
components? I supplied a 2nd macro below which just moves the output to
different column so I can make the two macro the same macro.

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 .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


Next RowCount

End With

End Sub

--------------------------------------------------------------------------------------------

same code with diffeent outputs

Sub CombineOrders()

With Sheets("bobines")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
PreviousOrder = 0
For RowCount = 2 To LastRow
Product = .Range("A" & RowCount)
NextProduct = .Range("A" & (RowCount + 1))
Order = .Range("B" & RowCount)
Order = Order + PreviousOrder

If Product = NextProduct Then
'product matches
NextOrder = .Range("B" & (RowCount + 1))
If Order <= 400 Then
If Order + NextOrder <= 400 Then
PreviousOrder = Order
Else
.Range("E" & RowCount) = Product
.Range("F" & RowCount) = Order
End If
Else
PreviousOrder = Order
End If
Else
'Product doesn't match put on bobbines
If Order <= 400 Then
.Range("E" & RowCount) = Product
.Range("F" & RowCount) = Order
Else
.Range("E" & RowCount) = Product
.Range("F" & RowCount) = 400
.Range("G" & RowCount) = Product
.Range("H" & RowCount) = Order - 400
End If

PreviousOrder = 0
End If
Next RowCount

End With

End Sub

 

[Joel]

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]

Hot stuff. Love it!

Its super close but there are just a few little things I’ve noticed.

Grouping doesn’t 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]

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]

Closer than close!!

Thanks a lot for your help and sorry to seek another mod, i'm sure it took a
while to get to here. Is it possible to check for one last problem -
disappearing results.

When I have four inputs, both of which will have two ouputs as the weights
are over 400 i loose one of results. Example below

----------------Results--------------
A 100 D 2100
A 2000 D 2000 A 100 D 100 D 4000 (2000 of A missing)

If i introduce a third product (replace the first D with a B), I get the
same result product A missing 2000. If I then make it the worst case, four
different products all overthe limit the results is two items missing
quantities.
--------------Results--------------
C 5000 B 2100 C 200 B 100 B 2000
A 2000 D 2000 A 0 D 0 D 2000

If the first two columns and are kept for the bobines that are <=400 and the
last two are for the multiples of 400 (whole full bobines only) maybe this is
the easiest way to ensure that all will be counted?

I have to send u some beer over for your efforts!

LiAD

 

[Joel]

the code worked fine. i just was over-writing the data column G

from

If .Range("E" & RowCount) = "" Then
NewCol = "E"
Else
NewCol = "G"
End If

to
If .Range("E" & RowCount) = "" Then
NewCol = "E"
Else
If .Range("G" & RowCount) = "" Then
NewCol = "G"
Else
NewCol = "I"
End If
End If


update 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
If .Range("G" & RowCount) = "" Then
NewCol = "G"
Else
NewCol = "I"
End If
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]

Looks like the money shot right there!

Thanks a lot