Help required to automate calculation

Not sure if there is a quick fix for this. I do not do much work with Excel
so I cannot help the user.

Our company manufactures rectangular tubes from a variety of materials
including glass fibre and resin-impregnated cotton and paper. The material
required is wrapped around a template (known as a mandrel) of the right
inner size. This is then baked in an industrial oven, and the mandrel
removed when it is cooled, resulting in the final product.

The problem we have is knowing which mandrel, or combinations of mandrels to
use, in order to get the right template size. We have over 400 different
mandrel sizes, and a varying quantity of each. However, we are regularly
asked for a size of tube where we have to combine several mandrels to get
the nearest fit.

For example, if we are asked for a tube measuring 72cm wide by 40cm high, we
may have to use for mandrels (two on top of another two) in order to meet
the requirement, such as

lower level: 25cm high x 30cm wide, next to 20cm high x 37cm wide
upper level: 15cm high x 35cm wide, next to 20cm high x 37cm wide

This will give a 'nearest match'.

The mandrel data is stored in an Excel spreadsheet, with the columns as
follows

col 1: Width
col 2: Height
col 3: Number of mandrels of this size

Our problems are as follows.

1). It can take literally hours to calculate manually which mandrels to use
to get the nearest match
2). We may get a problem where two or more mandrels of the same size are
needed but we do not have enough of them.

Can any of you Excel experts think of an automated method for calculating
the best combination of mandrels required to achieve the nearest match,
taking into account the number of mandrels available for each size?

Any help would be gratefully appreciated.

Please remember that I am not an Excel expert, never having written an Excel
macro, although I do know a reasonable amount about MS Access (not including
VBA).

Regards

Tom

Tom
That sounds like a puzzle and I'm into puzzles. If you wish, send me a
file with your listing of mandrels as you describe.
But I also need for you to send me some examples and solutions to these
examples. In other words, give me a size that you might need, then give me
what you consider to be an acceptable solution for that need. Give me
several examples. You could use some previous jobs that you have done.
You use the term "nearest match". That is a wide-open deep hole and I
can't work with that, unless you truly mean that any degree of "close" is
acceptable if it is the closest match possible given the restraints you
describe. In other words, I can see where a 1 unit (of measure) miss is
acceptable for one job and a 10 units miss is acceptable for another job, if
in both cases that is as close as one can get. Is that correct?
My email address is [email protected]. Remove the "nop" from this
address. HTH Otto
"Tom" <[email protected]> wrote in message
news:[email protected]...
> Not sure if there is a quick fix for this. I do not do much work with
> Excel so I cannot help the user.
>
> Our company manufactures rectangular tubes from a variety of materials
> including glass fibre and resin-impregnated cotton and paper. The material
> required is wrapped around a template (known as a mandrel) of the right
> inner size. This is then baked in an industrial oven, and the mandrel
> removed when it is cooled, resulting in the final product.
>
> The problem we have is knowing which mandrel, or combinations of mandrels
> to use, in order to get the right template size. We have over 400
> different mandrel sizes, and a varying quantity of each. However, we are
> regularly asked for a size of tube where we have to combine several
> mandrels to get the nearest fit.
>
> For example, if we are asked for a tube measuring 72cm wide by 40cm high,
> we may have to use for mandrels (two on top of another two) in order to
> meet the requirement, such as
>
> lower level: 25cm high x 30cm wide, next to 20cm high x 37cm wide
> upper level: 15cm high x 35cm wide, next to 20cm high x 37cm wide
>
> This will give a 'nearest match'.
>
> The mandrel data is stored in an Excel spreadsheet, with the columns as
> follows
>
> col 1: Width
> col 2: Height
> col 3: Number of mandrels of this size
>
> Our problems are as follows.
>
> 1). It can take literally hours to calculate manually which mandrels to
> use to get the nearest match
> 2). We may get a problem where two or more mandrels of the same size are
> needed but we do not have enough of them.
>
> Can any of you Excel experts think of an automated method for calculating
> the best combination of mandrels required to achieve the nearest match,
> taking into account the number of mandrels available for each size?
>
> Any help would be gratefully appreciated.
>
> Please remember that I am not an Excel expert, never having written an
> Excel macro, although I do know a reasonable amount about MS Access (not
> including VBA).
>
> Regards
>
> Tom
>