Compare and sum
 

Hi,

I am trying to look at an excel tool to decide what weights should be
manufactured onto bobines for copper cables.

I need a formula that will look through a list and create a subtotal of the
same items that are adjacent on a list IF the total of their weights is less
than 400 OR a combined weight that is multiple of 400 that can be separated
into weights of 100<weight<400. If these conditions cannot be met we must
accept the weight of the product as it is even if its outside the 100 or 400
barrier. (For your ref it means more work as it has be cut into smaller
pieces before the next process so it adds a step, hence why we group if
possible). So from the following table with inputs in cols A & B I need to
create the subtotal in col C.

XN12 F0.5A 50
XN12 F0.5A 100
XN12 F0.5A 200 350
YN8 F0.3 350
YN8 F0.3 150 500 (will be manuf in2 lots)
XN12 F0.5A 100 100
ZN11 F0.5A 450 450
ZN6 F 0.4B 75 75
ZN6 F 0.4B 350 350 (cant be split as outside
limits)
XN12 F0.5A 50
XN12 F0.5A 100 150

Note that in the above example I have purposely repeated the same code in
several places (adjacent and not adjacent) to show that the formula must only
look in adjacent cells. Also the code to match comes from an A1&B1&R1 type
formula to combine various conditions that need to be matched into one
series, so it might look like junk but it has relevance.

Does anyone know what sort of iterative type formula I could use to arrive
with the results in Col C?

Thanks
LiAD

Compare and sum
 

Hi,
Not sure what you need, if you want the sum by item you can have a list of
your items in let's say column C starting in C2 so in D2 enter

=sumproduct(--($A$1:$A$1000=C2),$B$1:$B$1000)

change range to fit your needs but remenber that the range has to be the
same in both parts of the formula

"LiAD" wrote:

Hi,

I am trying to look at an excel tool to decide what weights should be
manufactured onto bobines for copper cables.

I need a formula that will look through a list and create a subtotal of the
same items that are adjacent on a list IF the total of their weights is less
than 400 OR a combined weight that is multiple of 400 that can be separated
into weights of 100<weight<400. If these conditions cannot be met we must
accept the weight of the product as it is even if its outside the 100 or 400
barrier. (For your ref it means more work as it has be cut into smaller
pieces before the next process so it adds a step, hence why we group if
possible). So from the following table with inputs in cols A & B I need to
create the subtotal in col C.

XN12 F0.5A 50
XN12 F0.5A 100
XN12 F0.5A 200 350
YN8 F0.3 350
YN8 F0.3 150 500 (will be manuf in2 lots)
XN12 F0.5A 100 100
ZN11 F0.5A 450 450
ZN6 F 0.4B 75 75
ZN6 F 0.4B 350 350 (cant be split as outside
limits)
XN12 F0.5A 50
XN12 F0.5A 100 150

Note that in the above example I have purposely repeated the same code in
several places (adjacent and not adjacent) to show that the formula must only
look in adjacent cells. Also the code to match comes from an A1&B1&R1 type
formula to combine various conditions that need to be matched into one
series, so it might look like junk but it has relevance.

Does anyone know what sort of iterative type formula I could use to arrive
with the results in Col C?

Thanks
LiAD