L

#### LiAD

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