Cut Smaller Pieces From Bigger Pieces

[Solon Aquila]

Where I work, I take large remnants of raw material and see if I can cut them down to smaller pieces so we have less waste. I've created a few spreadsheets that allow me to do this a bit more efficiently.
Once I've decided which piece to cut, the meat of the operation lies in being able to get the most pieces from the original block.
It took me a bit to work out but I have a spreadsheet that looks at all possible cutting combinations (how I did it is a separate tab).
The attached image (sorry, I can't get xl2bb to load) shows 6 source to work piece combinations.
Each uses formulas to determine the number of pieces yielded by that combination.
In case multiple combinations yield the same number of pieces, I use 'which leaves the largest individual piece of leftover' as the decider. That's not AS critical, but it gives more options for getting another part from the leftover.
I'm trying to figure out how to write a script that will look at the source piece and work piece dimensions and work out which combination gives the best yield / leftover combination.
I think I can set up variables for each dimension and use that spreadsheet as a basis for figuring out how to do the calculations.
•
So, if I take a look at the formulas I used in the spreadsheet, can I basically assign variables to the results of each calculation, then compare each result variable to determine which is best.
Kind of (currently ignoring largest leftover piece):
Sl= source length cell Pl= piece length cell RA=Result A RC=Result C RF=Result F
Sw=source width cell Pw= piece width cell RB=Result B RD=Result D
Sh=source height cell Ph= piece height cell RC=Result C RE=Result E
(Sl÷Pl) × (Sw÷Pw) × (Sh÷Ph)= RA
(Sl÷Ph) × (Sw÷Pl) × (Sh÷Pw)= RB
(Sl÷Pw) × (Sw÷Ph) × (Sh÷Pl)= RC
(Sl÷Pl) × (Sw÷Ph) × (Sh÷Pw)= RD
(Sl÷Pw) × (Sw÷Pl) × (Sh÷Ph)= RE
(Sl÷Ph) × (Sw÷Pw) × (Sh÷Pl)= RF

In a standard spreadsheet, I would go to the bottom of the results column and say "MAX(RA, RB, RC, RD, RE, RF, RG)" but I don't know how to do that in a script.

So, if it looks like the 'code' above appears to be a workable way to test all of the options, how the heck do I say "pick the max of these" in a script?

TIA

 

[PeteWright]

If there is no need for a Script then just use Names instead of Cell References.
Working with a large range of cells personally I find it much easier to keep track of the "variables" when the have clear names.

For example if you have cells A1, B1 and C1:
A1 = m (Mass) = 1 [kg]
B1 = a (Acceleration) = 1 [m/s²]
C1 = F (Force) = m * a = 1 [N]

you could name them accordingly m, a and F:

Does that help?

If not, please let me know, what "Script" you are writing about.
Office Script? VBA?

 

[NateSC]

The VBA would be something like:

RA = (Sl / Pl) * (Sw / Pw) * (Sh / Ph)
RB = (Sl / Ph) * (Sw / Pl) * (Sh / Pw)
RC = (Sl / Pw) * (Sw / Ph) * (Sh / Pl)
RD = (Sl / Pl) * (Sw / Ph) * (Sh / Pw)
RE = (Sl / Pw) * (Sw / Pl) * (Sh / Ph)
RF = (Sl / Ph) * (Sw / Pw) * (Sh / Pl)

myTest = WorksheetFunction.Max(RA, RB, RC, RD, RE, RF)

However, I found that all the variables end up with the same number. You have the same equation 6 times when you work it out. It can be rewritten as (Sl*Sw*Sh)/(Pl*Pw*Ph). The variation in the formulas only mixes up the order, but they all resolve to the same equation.

 

[Snakehips]

@Solon Aquila
As @NateSC has said, if you are using vba then use WorksheetFunction.Max(?,?,?)
I obviously don't know your full circumstances but I do wonder how much benefit there will be in coding this as opposed to utilising basic Excel?
Also it does occur to me that any combinations that yield the same quantity of pieces will leave exactly the same 'volume' of leftover material.
So, is a tie-breaker based on the max bit of leftover (of whatever length?) of any real value?

Anyway, for what it may be worth, here is my non-tie-break formula effort.

Testing Formula Update.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2		ENTER DIMENSIONS					Helper Columns (Can Hide or Move Elsewhere)
3		Block Dims >	91.25	67.5	55			13	10	8
4		Piece Dims >	6.75	5.5	9.125		Combo Qtys	16	12	10
5		Best Cutting Option						10	7	6
6		Use Block Dim	91.25	67.5	55
7		To Cut Piece Dim	9.125	6.75	5.5		QTY		Piece Dim
8		Cut Qty	10	10	10		936	6.75	5.5	9.125
9		Total Pieces	1000				910	6.75	9.125	5.5
10							960	5.5	6.75	9.125
11							1000	9.125	6.75	5.5
12							896	5.5	9.125	6.75
13							960	9.125	5.5	6.75
14
Sheet5

Cell Formulas
Range	Formula
H3	H3	=QUOTIENT(C3,C4)
I3	I3	=QUOTIENT(D3,C4)
J3	J3	=QUOTIENT(E3,C4)
H4	H4	=QUOTIENT(C3,D4)
I4	I4	=QUOTIENT(D3,D4)
J4	J4	=QUOTIENT(E3,D4)
H5	H5	=QUOTIENT(C3,E4)
I5	I5	=QUOTIENT(D3,E4)
J5	J5	=QUOTIENT(E3,E4)
C6:E6	D6	=D3
C7:E7	D7	=INDEX(I8:I13,MATCH(MAX($G8:$G13),$G8:$G13,0))
C8:E8	D8	=QUOTIENT(D6,D7)
G8	G8	=H3*I4*J5
H8:J8	H8	=C4
G9	G9	=H3*I5*J4
H9	H9	=C4
I9	I9	=E4
J9	J9	=D4
G10	G10	=(H4*I3*J5)
H10	H10	=D4
I10	I10	=C4
J10	J10	=E4
G11	G11	=H5*I3*J4
H11	H11	=E4
I11:J11	I11	=C4
G12	G12	=H4*I5*J3
H12:I12	H12	=D4
J12	J12	=C4
G13	G13	=H5*J3*I4
H13	H13	=E4
I13	I13	=D4
J13	J13	=C4
C9	C9	=MAX(G8:G13)

HTH

 

[Solon Aquila]

If there is no need for a Script then just use Names instead of Cell References.
Working with a large range of cells personally I find it much easier to keep track of the "variables" when the have clear names.

For example if you have cells A1, B1 and C1:
A1 = m (Mass) = 1 [kg]
B1 = a (Acceleration) = 1 [m/s²]
C1 = F (Force) = m * a = 1 [N]

you could name them accordingly m, a and F:
View attachment 111627View attachment 111628

Does that help?

If not, please let me know, what "Script" you are writing about.
Office Script? VBA?

Pete,
Thanks for the reply, I'm probably putting things poorly so it's harder to understand I'm trying to do.
I also may be using the word 'script' inaccurately. I've never been really clear on, say, the difference between a macro and a script and the use of VBA in Excel (I always viewed them as the same).
I have a spreadsheet that works out which of a series of cuts provides the best yield (the image I attached). But it has to display ALL results and just highlights the best one. The 'pretend script' info I put up there is based on the formulas I already use in the cells.
Right now, I enter the source piece info and work piece info and looking at 6 sets of results with the best one highlighted.

What I want to do is enter the source piece info and work piece info, have it crunch the information to determine which cut pattern provides the best yield, then display only that cut pattern information.
I thought a macro / script / VBA code could provide that.
And, yeah, I'd rather have more identifiable variables, but I'm working with length, wide, and height of two different pieces, and what I have up there actually kind of works for my brain. If I could figure out a way to make 'sub' characters that kinda hang a little bit down from the line (like in how they write chemical formulas), I would.

Does that kind of explain?

 

[Solon Aquila]

The VBA would be something like:

RA = (Sl / Pl) * (Sw / Pw) * (Sh / Ph)
RB = (Sl / Ph) * (Sw / Pl) * (Sh / Pw)
RC = (Sl / Pw) * (Sw / Ph) * (Sh / Pl)
RD = (Sl / Pl) * (Sw / Ph) * (Sh / Pw)
RE = (Sl / Pw) * (Sw / Pl) * (Sh / Ph)
RF = (Sl / Ph) * (Sw / Pw) * (Sh / Pl)

myTest = WorksheetFunction.Max(RA, RB, RC, RD, RE, RF)

However, I found that all the variables end up with the same number. You have the same equation 6 times when you work it out. It can be rewritten as (Sl*Sw*Sh)/(Pl*Pw*Ph). The variation in the formulas only mixes up the order, but they all resolve to the same equation.

NateSC - Thank you for the code snippet, especially the "myTest=WorksheetFunction.Max(RA, RB, RC, RD, RE, RF)" part. That last bit is where my mind has been getting stuck for now.
Though Pete's reply has me thinking I need to first figure out the difference between a macro, a script, and VBA code. I always thought they were the same.
As to them providing the same result, I'll have to take another look and ponder what I put into the spreadsheet vs what I typed for for the 'pretend code'. In the spreadsheet, I get between 896 and 1000 pieces with differing amounts of leftover.

 

[Solon Aquila]

@Solon Aquila
As @NateSC has said, if you are using vba then use WorksheetFunction.Max(?,?,?)
I obviously don't know your full circumstances but I do wonder how much benefit there will be in coding this as opposed to utilising basic Excel?
Also it does occur to me that any combinations that yield the same quantity of pieces will leave exactly the same 'volume' of leftover material.
So, is a tie-breaker based on the max bit of leftover (of whatever length?) of any real value?

Anyway, for what it may be worth, here is my non-tie-break formula effort.

Testing Formula Update.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2		ENTER DIMENSIONS					Helper Columns (Can Hide or Move Elsewhere)
3		Block Dims >	91.25	67.5	55			13	10	8
4		Piece Dims >	6.75	5.5	9.125		Combo Qtys	16	12	10
5		Best Cutting Option						10	7	6
6		Use Block Dim	91.25	67.5	55
7		To Cut Piece Dim	9.125	6.75	5.5		QTY		Piece Dim
8		Cut Qty	10	10	10		936	6.75	5.5	9.125
9		Total Pieces	1000				910	6.75	9.125	5.5
10							960	5.5	6.75	9.125
11							1000	9.125	6.75	5.5
12							896	5.5	9.125	6.75
13							960	9.125	5.5	6.75
14
Sheet5

Cell Formulas
Range	Formula
H3	H3	=QUOTIENT(C3,C4)
I3	I3	=QUOTIENT(D3,C4)
J3	J3	=QUOTIENT(E3,C4)
H4	H4	=QUOTIENT(C3,D4)
I4	I4	=QUOTIENT(D3,D4)
J4	J4	=QUOTIENT(E3,D4)
H5	H5	=QUOTIENT(C3,E4)
I5	I5	=QUOTIENT(D3,E4)
J5	J5	=QUOTIENT(E3,E4)
C6:E6	D6	=D3
C7:E7	D7	=INDEX(I8:I13,MATCH(MAX($G8:$G13),$G8:$G13,0))
C8:E8	D8	=QUOTIENT(D6,D7)
G8	G8	=H3*I4*J5
H8:J8	H8	=C4
G9	G9	=H3*I5*J4
H9	H9	=C4
I9	I9	=E4
J9	J9	=D4
G10	G10	=(H4*I3*J5)
H10	H10	=D4
I10	I10	=C4
J10	J10	=E4
G11	G11	=H5*I3*J4
H11	H11	=E4
I11:J11	I11	=C4
G12	G12	=H4*I5*J3
H12:I12	H12	=D4
J12	J12	=C4
G13	G13	=H5*J3*I4
H13	H13	=E4
I13	I13	=D4
J13	J13	=C4
C9	C9	=MAX(G8:G13)

HTH

Snakehips,
Basic Excel? That sounds like yet another kind of 'code' or something. As I say in my other replies, I'm finding out that macros, scripts, etc in Excel appears to have a lot more options than I ever realized.
So, I'll say that I'm starting out trying to find how to make it so Excel accepts my length/width/height info for the source block and the length/width/height info for the workpiece, determines the best cut pattern based on yield first and leftover size second (I'll get to that in a minute) and displays ONLY the cut pattern information that is best.
As to the importance of the size of the leftover piece. Number of pieces yielded is the top priority, but if two cut patterns yeild the same number of pieces, then I want the largest piece of leftover between those two options. The reason for big leftover is that I might be able to cut a 12 × 12 × 12 piece of leftover into a different, smaller part whereas I may not be able to turn 8 pieces of 6 × 6 × 6 into smaller parts. Bigger leftover just gives me more options for futher cuts.
When I first started playing around with my yield, I did a simple 'which one provides the most cuts' and found myself sometimes recycling a number of small pieces that, were they a little bigger, could have been reused. So I put the "Biggest Piece Leftover" as tie-breaker.
I'm not married to any one method (e.g., VBA, macro, script, code, etc) of solving this. If it's possible in basic Excel to NOT show all 6 results with the best highlighted, I'm good.
I do hope that whatever I use I can add later if I find there are things I want to add or change. But, that's future and only possibility right now.
By the way - the leftover is NOT the total leftover or anything. It's simply a one cut value. I'm not going to even TRY to crunch enough numbers to figure out actual, individual volumes.

 

[Snakehips]

@Solon Aquila , by Basic Excel I meant using standard Excel functions to create formulas within a workbook/worksheet, just as I assume you will have done within your posted image sheet.
More presumptive waffle......
Latest versions of Excel do indeed offer many other 'options' in the way of features for computation, analytics and automation. However, majority of users will only ever need to use a minute proportion of what Excel has to offer. Regarding 'code', I am pretty confident that you are meaning the use of VBA. VBA is the programming language associated with Macros, be they recorded or purpose-written. A well constructed Macro can certainly be a powerful option, especially when looking to automate a repetitive task. However, Macros are not necessarily a silver bullet, solving-wise, compared to using standard Excel.
Have you tested the solution I posted? If not, then test it in an otherwise empty sheet of your workbook. I'm hopeful that might be close to what you are wanting.
Click on the Copy icon at the top left corner of the XL2BB MiniSheet. Then Paste into cell A1 of the empty sheet

You can then enter Block Dimensions in C3:E3 The order of dimension does not matter.
Enter the Piece Dimensions in C4:E4. The order of dimension does not matter.

The best cutting option* should then be automatically displayed below. (Hence why might you need a macro?)
* Based solely on piece yield. Does not include a tie-breaker.

The orange cells are 'helpers' for computational purposes and may be hidden.

If the above does prove to be of value then I will happily try and help you incorporate a tie-breaker if needed.

Regarding offcuts. Volume-wise the offcuts will be the same for similar piece yield.
OffcutVol = BlockVol-(PieceVol*PieceQty)
However, the dimensions of the offcuts may vary dependent upon the relative dimensions of the cuts.

My apologies if I have anything or all of this wrong.

 

[NateSC]

NateSC - Thank you for the code snippet, especially the "myTest=WorksheetFunction.Max(RA, RB, RC, RD, RE, RF)" part. That last bit is where my mind has been getting stuck for now.
Though Pete's reply has me thinking I need to first figure out the difference between a macro, a script, and VBA code. I always thought they were the same.
As to them providing the same result, I'll have to take another look and ponder what I put into the spreadsheet vs what I typed for for the 'pretend code'. In the spreadsheet, I get between 896 and 1000 pieces with differing amounts of leftover.

@Solon Aquila -
In VBA (which is the same as a macro) the format is "variable = value or equation or whatever you determine what goes in the variable".
A macro is written in VBA - Visual Basic for Applications (the language which is a derivative of VB-Visual Basic). To oversimplify it, VBA has shortcuts to work better within the Microsoft Applications. You will find it is slightly different in each application (Word, Excel, Outlook, etc.). In Excel VBA, not all of the functions you use in a worksheet are in VBA, but you can call them using "WorksheetFunction."

I saw your first post about this project and was following it. There are countless ways to make your project work. You can have sheets with the Excel standard formulas, custom formulas, full macros, or any combination of these. I have been thinking that since your sheets already work and your major issue is making sure all the inputs are present, you could just create a simple VBA front end that guides the user through the necessary inputs before it allows them to see the results. You could possibly build this front end in non-macro Excel using conditional formatting to make things "appear" and "disappear" (though this can get a little complicated). In the end, it is 100% your choice how you build your application.

 

[Solon Aquila]

@Solon Aquila -
In VBA (which is the same as a macro) the format is "variable = value or equation or whatever you determine what goes in the variable".
A macro is written in VBA - Visual Basic for Applications (the language which is a derivative of VB-Visual Basic). To oversimplify it, VBA has shortcuts to work better within the Microsoft Applications. You will find it is slightly different in each application (Word, Excel, Outlook, etc.). In Excel VBA, not all of the functions you use in a worksheet are in VBA, but you can call them using "WorksheetFunction."

I saw your first post about this project and was following it. There are countless ways to make your project work. You can have sheets with the Excel standard formulas, custom formulas, full macros, or any combination of these. I have been thinking that since your sheets already work and your major issue is making sure all the inputs are present, you could just create a simple VBA front end that guides the user through the necessary inputs before it allows them to see the results. You could possibly build this front end in non-macro Excel using conditional formatting to make things "appear" and "disappear" (though this can get a little complicated). In the end, it is 100% your choice how you build your application.

Nate,
Thank you very much for this and, yes, I'm rapidly learning that there are lots of ways to make this happen. This is going to be a bit of fun, I'll say that!