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