AREAS

Hi Tam,

Excel is a great program for this type of problem. However, the problem you describe falls in the mathematical category of Tiling or "Tessellation", and there are many such problems that cannot be easily solved. Because your problem involves only 3 sizes and (hopefully) a standard-sized sheet that will hold at maximum only a few dozen labels it is probably possible to come up with an algorithm that will handle all your ratios. Some wasted space will probably occur in most cases.

One thing that you didn't mention: is it acceptable to lay out labels in different orientations (some vertically, some horizontally)? Do they have to have any space between them? Are the labels all rectangular? Are there any other restrictions on the layout?

Since in Excel it is possible to write macros that manipulate Shape and Drawing objects, it would even be possible to have Excel automatically display the layout it comes up with, although this might require several hours of programming.

This is an interesting problem, but probably a bit too large to get a complete answer for it on this message board. If you don't get an answer and it is worth hiring a consultant to solve, you might want to post a request on this board for consulting quotes and ask MrExcel for a quote. It might be less than you would expect. To get a formal quote, you would need to explain the problem more thoroughly, especially with respect to any restrictions on the layout, and describe how you want the answer presented.

I hope this helps.

Damon

Thanks Damon,

Iam glad you replied, I am still clueless as to how I would apply this type of formula,
My problem is this (or different variations of this): I get an order for labels for whisky bottles-sometimes front and back labels, sometimes front, back and neck labels, other times only fronts. The quantities and sizes of each can vary i.e 10000 fronts: 50 x 90mm; 20000 backs:50 x 50mm and 5000 necks 90 x 20.(always rectangular, no matter the shape of the label)
This is just an example but each size of label has a wide edge and a deep edge. The wide side of the label must run along the grain of the paper, A popular size of paper is B2:520(WIDE) X 720(DEEP)MM.

My job is to work out the best layout for this particular order (quickly) using the least amount of sheets possible.

Each job will vary due to the nature of the print i.e the gaps at the top and bottom will change per order and the gaps in between the labels can be non existent or up to 25mm although mostly constant.

I would like to solve this problem myself using tesselation, but I would need some pointers,

Ideally as with more simple calculations I would type in the Quantities, Sizes, gaps between and gaps at the top and bottom and also each edge.
Then, excel would work out the rest!

A display of the layout would be superb but maybe a bit complex for me to do myself. The answer would Ideally be presented the easiest way to understand.
A typical layout could be:
Along the top (wide) edge:5 fronts(wide)&1 neck(wide)6mm gaps in between. 3mm either side.
Along the right hand side(deep):7 necks(deep) 6mm gaps in between, 10mm gap at top, 15mm gap at bottom.
Along the left hand side(deep): 3 fronts (deep), 1 back(deep), 6 mm gaps in between,10mm gap at top, 15mm gap at bottom.
Along the bottom(wide): 9 backs(wide),1 neck(wide)
6mm gaps between,3mm either side.

This makes a sheet with 15 fronts, 7 necks and 9 backs. It is difficult to explain without diagrams but I hope you get the idea. Any more help or a free quote would be appreciated. Cheers! TAM.