Calculating three most efficient lengths

[Nanaia]

What formula, or combination of formulae, would be uses to look at all the numbers in a column and calculate the three sheet lengths they would fit best on. (does that make sense?)
Goal: To determine which three sheet lengths would be most efficient for the part lengths to nest into for a given job (metal coil industry). Minimum sheet length 86”, maximum 150”. Sheets are cuts in increments of 6”.
I understand which formulas to use to find out the largest number in the column, the shortest number in the column, and the number that occurs the most often. I’m not sure if I can accomplish my goal on this, but I figured this is Excel, there’s a way to do this.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-general/1227194-calculating-three-most-efficient-lengths.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Nanaia]

My apologies. I didn't realize the two sites were connected. I'll add the links.

 

[Nanaia]

https://www.excelforum.com/excel-general/1227194-calculating-three-most-efficient-lengths.html

 

[markmzz]

What formula, or combination of formulae, would be uses to look at all the numbers in a column and calculate the three sheet lengths they would fit best on. (does that make sense?)
Goal: To determine which three sheet lengths would be most efficient for the part lengths to nest into for a given job (metal coil industry). Minimum sheet length 86”, maximum 150”. Sheets are cuts in increments of 6”.
I understand which formulas to use to find out the largest number in the column, the shortest number in the column, and the number that occurs the most often. I’m not sure if I can accomplish my goal on this, but I figured this is Excel, there’s a way to do this.

Hi!

Some thing like this:

	A	B	C	D	E	F	G	H	I
1	Default sheets		Sheet1	Sheet2	Sheet3	Sheet4	Total	Best Default Sheet
2	84		90				90	96
3	90		44	45	35		124	126
4	96		30	35	40	20	125	126
5	102		15	10	20	30	75	84
6	108		70	68			138	144
7	114		73	40			113	114
8	120		71	67			138	144
9	126		123				123	126
10	132		101	27			128	132
11	138		105	40			145	150
12	144		20	27	100		147	150
13	150
14
***	**************	**	********	********	********	*******	********	*****************	**

<tbody>
</tbody>

If not, give to us more details. With a example (what you have and what you want) like the table above.

Markmzz

 

[Nanaia]

markmzz - Your table is very close to what I'm looking for...providing I'm understanding what I'm viewing. The default sheet sizes is exact. I'm not sure I understand how the rest of it is organized or what the relevance of the numbers are though.
Thank you for helping.

 

[markmzz]

If not, give to us more details. With a example (what you have and what you want) like the table above.

markmzz - Your table is very close to what I'm looking for...providing I'm understanding what I'm viewing. The default sheet sizes is exact. I'm not sure I understand how the rest of it is organized or what the relevance of the numbers are though.
Thank you for helping.

Ok.

The default sheet sizes is exact.​

But, we need more details. With a example (what you have and what you want).

Markmzz

 

[Nanaia]

I'm trying to figure out how to word it...
Part of our job is to chose what three sheet lengths need to be cut from the coil for the job that have the least amount of waste and tell our compiler. We make panels that vary in length and we have a program that "elaborates" (nests? compiles? groups?) the panels for job onto the three sheet sizes we tell it to use. The CSV for the job has all the flat lengths listed in one column (column S). I wanted to be able to have another worksheet in the background (meaning it is in the same file and references the CSV worksheet) that looks at column S from the CSV says (somehow) what three sheet length sizes would be best suited for the job so we have the least amount of waste. Some of our sheets are painted with metallic paint, so to keep it so all pieces look right when installed we keep it so the length is always going to be the length, never the width so when two pieces are side by side they are the same colour. Looking at this I see I may need to have several steps involved, which is fine. I'm asking for help to narrow down what the steps are.
Please correct me if I'm wrong, regarding the table Markmzz posted - are the "sheet 1", "sheet 2" column headings referring to the part lengths and the totals in the cells listed in the "sheet" columns are the number of times the part fit on the sheet? Just looking for clarification.
Thank you.

 

[markmzz]

I'm trying to figure out how to word it...
Part of our job is to chose what three sheet lengths need to be cut from the coil for the job that have the least amount of waste and tell our compiler. We make panels that vary in length and we have a program that "elaborates" (nests? compiles? groups?) the panels for job onto the three sheet sizes we tell it to use. The CSV for the job has all the flat lengths listed in one column (column S). I wanted to be able to have another worksheet in the background (meaning it is in the same file and references the CSV worksheet) that looks at column S from the CSV says (somehow) what three sheet length sizes would be best suited for the job so we have the least amount of waste. Some of our sheets are painted with metallic paint, so to keep it so all pieces look right when installed we keep it so the length is always going to be the length, never the width so when two pieces are side by side they are the same colour. Looking at this I see I may need to have several steps involved, which is fine. I'm asking for help to narrow down what the steps are.
Please correct me if I'm wrong, regarding the table Markmzz posted - are the "sheet 1", "sheet 2" column headings referring to the part lengths and the totals in the cells listed in the "sheet" columns are the number of times the part fit on the sheet? Just looking for clarification.
Thank you.

Hi!

To make the table of the post #5, I used the below text (post for you in another forum):

"For example, if the part is 90" long it would fit on a 96" sheet more efficiently than on a 150" sheet. Also, a 44"plus a 45" and a 35" would bit better on a 126" sheet than144" sheet or even a 120" sheet because there is less waste."

You're last post help a little, now post a table with examples (what you have and what you want - 5 examples or more).

Markmzz

 

[Nanaia]

I'll try.


This is from my Ref2 (reference) sheet

Panel Lengths
124.209	1st most common
106.709	2nd most common
31.709	3rd most common
70.549	4th most common
63.709	5th most common
124.209	Longest
9.049	Shortest
31.439	Widest
14.417	Narrowest

<tbody>
</tbody><colgroup><col><col></colgroup>

This is columns S and T in the CSV worksheet

Face Len	Face Width
119.25	23.25
119.625	23.25
102.125	23.25
78.125	23.25
45.125	23.25
26.125	23.25
107.125	23.25
19.5	23.25
15.5	23.25
96.5	23.25
34.5	23.25
77.75	23.25
37.75	23.25
104.75	23.25
115.5	23.25
64.75	23.25
6	23.25
84.5	23.25
101.75	23.25
26.75	23.25
67.5	23.25
58.75	23.25
42.5	23.25
111.5	23.25
58.5	23.25
119.625	9.75
119.25	9.75
102.125	9.75
119.625	24.75
119.25	24.75
102.125	24.75
32.75	24.75
60.5	24.75
29.75	24.75
15.5	24.75
39.5	24.75
119.625	26.75
119.25	26.75
102.125	26.75
119.625	22.25
119.25	22.25
102.125	22.25
119.625	10.25
119.25	10.25
102.125	10.25
119.25	23.25
119.25	26.75
119.25	23.25
119.25	10.25
119.25	22.25
66	23.25
83.5	23.25
83.5	22.25
83.5	26.75
47.125	24.75
47.125	23.25
66	10.25
83.5	9.75
119.25	9.75
119.25	9.75
119.25	23.25
119.25	26.75
119.25	23.25
119.25	22.25
119.25	10.25
93.125	23.25
93.125	24.75
93.125	26.75
93.125	22.25
93.125	10.25
93.125	9.75
66	23.25
119.25	23.25
119.25	24.125
66	24.125
119.25	23.25
119.25	24.125
63.125	23.25
63.125	24.75
63.125	26.75
63.125	22.25
63.125	10.25
63.125	9.75
63.125	22.25
119.625	22.25
119.25	23.875
66	23.875
71.375	9.75
71.375	23.25
71.375	24.75
71.375	22.25
71.375	18.25
71.375	12.25
71.375	26.75
71.375	22.25
59.75	23.25
119.25	22.25
119.25	11.25
119.25	23.875
59.75	10.25
93.125	22.25

<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2673;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2929;"> <tbody> </tbody>