T
Tom
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
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