Is this impossible?

[Guest]

I have a series of numbers in Column F that are tied to part numbers in colum
A. These part numbers remain the same each day, however the quantities for
each part changes daily. Currently I am required to look at each part number
and select the part number, whose quantities add up to 60. Some parts may
have quantities that are greater than 60 and require multiple skids. Is
there a way for excel to analize the numbers and sort which numbers need to
be combined in order to create a skid of 60 parts.

Thanks in advance

 

[Guest]

In column g add the fomula
=(f1/60) + 1

Column g will be the nmumber of skids for each part

 

[Guest]

Column A Column F
A 20
C 30
B 15
G 80
F 40

What I would like excel to do is look at data and list what part numbers can
be combined to total 60. I would also like for excel to look at the data and
(for example) list part G 60 then part G 20 and part F 40 for a total of 60.

 

[Guest]

This is a mathematical Packing problem that mathematicans have been working
on foreever. What is tthe best way to pack the parts. You need to get an
algorithm. the alogirithm would then have to be converted to an Excel Macro.

The 1st step would be to sort the part by quantities. Any quantites greater
than 60 should be put in the on box (or boxes) and the remainder left to be
combined with other part numbers. Usual algorithms involve taking the
highest quantity (like 59) and combining it with the lowest quantity (like 1)
so you get the fewest number of different parts in the same crate.

The easiest was of solving the problem is to just go in order of the sorted
part numbers and put 60 items in each box. This could be done using formulas
in excel
use
column g as the count of 60
column h the number of pice of the part to put in box
column h the box number

put in G2 the formula =mod(f1,60)
in G3 and copy G3 to the other cells in column G
=mod(F3+G2,60)

put in h2 the number =60*(G2/60)
in H3 and copy to othe cells in column h
=(60*((F3+G2)/60))-G2

put in i2 the number 1
in i3 and copy to othe cells in column i
((F3+G2)/60)+i2