Hi,
Wondering if someone could help me, I'm an estimator working for a fencing company,
I have a table that you enter all the different lengths of fence and it works out the number of panels required.
On most lengths you have to cut the last panel down in order for it to fit.
The offcut for this panel can then be used if a smaller last gap arises on another length.
So you need to look through all the lengths and find a gap that is smaller than the offcut on each individual length
This is quite simple when using the small function, however obviously the same offcut cannot be used twice, so I need someway to exclude offcuts that have already been used.
See below
@Clark_D From your picture it seems that you find a matching gap for an offcut by looking for the next smallest gap number in the list. Unless I misunderstood, there is a flaw in this approach. E.g. you match gap 3 to offcut 3. This makes no sense as offcut 3 is the result of closing gap 3.
Your challenge is greater than just finding a matching gap for a particular offcut. I replicated your model based on the picture you provided (see attached). I used dynamic arrays to speed things up, but they may not work for you if you are on an older Excel version. Hopefully the picture will clarify my approach. If not, let me know.
You would want to minimise waste by using offcuts as effectively as possible and perhaps use one offcut to close multiple gaps. For instance, offcut number 8 could be used to close gaps 1, 4, 5 and 6 leaving zero waste. Then, use offcut 7 to close gap 3 with some left-over and scrap offcut 2. Total waste would then be 0.4438 of one standard length or 1.3 meters. I've done this manually in columns I:J with some SUMIF formulae at the bottom. Conditional formatting was used to visualise that there are is no offcut once a gap is closed and to avoid that gaps are matched to be closed by its own offcut.
Of course, there are other combinations possible and perhaps one of them results in less overall waste. In your picture, you only look at 8 fences, and it's not too much trouble to do this manually. But I'm aware that this process may not be practical if your real life situation needs to deal with dozens of fences for which you want to optimise overall material usage. Perhaps the Solver add-in can be used to automate this, but goes beyond my area of expertise.
