Formula help to find how many lengths of an material per column of figures

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi John,

Think I've sorted it, it shows the sizes to make each fence and the same for the flower beds. I've put on what the total wood needed to make each fence/bed is, but then when we looked we saw a big amount of wastage.

So we was thinking instead of cutting per fence, excel could work out the optimal amount of fence timber we can use.

Example

It would put 5 smaller sizes with a few larger ones to meet the 5000mm and carry on that way to maximise

Thanks

150 sizes is going to be an issue!

Can these not be reduced to a much more reasonable range: think of "standard" fence panelling of 6' & 6': you would not go and buy a multitude of sizes.

Perhaps spilt the sizes between those for fencing (why potential so variable/) and flower beds.

Originally Posted by JohnTopley

150 sizes is going to be an issue!

Can these not be reduced to a much more reasonable range: think of "standard" fence panelling of 6' & 6': you would not go and buy a multitude of sizes.

Perhaps spilt the sizes between those for fencing (why potential so variable/) and flower beds.

The thing is that we are custom making the fences? I just thought Excel would use a type of sum or sort formula to arrange the figures to meet the maximum 5000mm

I know what you mean though, we did a ball park figure and against the total of all the sizes divided by 5000 it was as big difference.

Yes: I realise you are doing a "bespoke" set of fencing:

But for example, how variable is the fence height and what distance do they run e.g 100ft run at 6ft, 50ft run at 7ft height ......

So you have to choose a small series of optimal heights: the problem per se is not Excel but a mathematical optimisation which can get very complex (and I am no mathematician).

yeah I think I understand, the smallest fence is 580 x 950 and the biggest is 500 x 1800. Once we do the frames we have mesh to staple onto them. the flower beds are same in a frame but we are using feather board on them.

the more I look at it the more I think it's a manual task for me (might need a few drinks while I do it) in that i will just click on a few sizes till they meet 5000 and then delete and mark one piece, then another group, delete and mark second piece and keep going.

Hello,

This is my first post on this forum :-)

I think I have a solution to your question. However it will be limited in its practicality due to a very high number of permutations you are facing.
What you are after is a number of permutations possible, out of certain set, that meet certain criteria. In your case, as I understand, the criteria is a length of a plank.
However what you can do it work on smaller sets and optimise them.

What I did:
1. I found a VB code on the Internet and modified it slightly by adding a criteria (which is a plank length).
2. How the file works:
a) in sheet 1, in range A3 and down you need to provide a list of values (in your case the list represents individual lenghts of wood pieces that you need). Keep this list short or your PC won't handle it.
b) in cell A1 you need to type in P which stands for Permutations
c) in cell A2 you need to type in value which matches exactly number of values you entered in cell A3 and down.
d) run macro

The output will be produced in a new sheet. Then what you need to do is:
a)get rid of duplicates (you can use advanced filter to do this)
b) then you may want to do 'text to column' to see all planks in separate columns as original outcome is produced in one column with numbes separated by comma as delimiter.

The code will still produce duplicates but it does not mind as you're after hierarchy of least waste cut- as I understand.


PS I struggle to add attachment. I will try to do it in another post.

The excel add-in solver could be a possible solution as oeldere says. I've set up a small model with about 20 different cut sizes, I've simplified it a bit by adding
4 * 1793 and 4 * 1792 to 8 * 1793 and added 1245 and 1244 as 1245 as well as 523 and 522 to 523.

The model could certainly be extended to include more different cut.

As solver is run in a loop there must be set a reference to solver in Visual Basic.

Alf