Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating a cut list
I have to cut PVC pipe for various jobs. These PVC pipes come in a 10 ft
length. I am trying to maximize the cuts and generate less scrap. i.e. The first column "A" is Qty, "B" is length (inches) and "C" is sum. Can I put this in a look up table?? and have it pick lengths "B" not to exceed 10 feet (120 inches) and continue on until all lengths are satisfied? This particular job requires 5 10 ft pieces, with about 5 feet scrap. A B C 2 26.00 52.00 3 24.00 72.00 1 22.75 22.75 2 22.00 44.00 2 21.25 42.50 2 20.00 40.00 4 13.00 52.00 1 11.75 11.75 2 11.00 22.00 2 10.00 20.00 1 9.75 9.75 2 9.00 18.00 4 7.75 31.00 2 6.50 13.00 2 5.50 11.00 2 5.38 10.75 2 4.75 9.50 2 3.50 7.00 26 2.13 55.25 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating a cut list
On Wed, 23 Jan 2013 20:18:55 -0500, "wabbleknee"
wrote: I have to cut PVC pipe for various jobs. These PVC pipes come in a 10 ft length. I am trying to maximize the cuts and generate less scrap. i.e. The first column "A" is Qty, "B" is length (inches) and "C" is sum. Can I put this in a look up table?? and have it pick lengths "B" not to exceed 10 feet (120 inches) and continue on until all lengths are satisfied? This particular job requires 5 10 ft pieces, with about 5 feet scrap. A B C 2 26.00 52.00 3 24.00 72.00 1 22.75 22.75 2 22.00 44.00 2 21.25 42.50 2 20.00 40.00 4 13.00 52.00 1 11.75 11.75 2 11.00 22.00 2 10.00 20.00 1 9.75 9.75 2 9.00 18.00 4 7.75 31.00 2 6.50 13.00 2 5.50 11.00 2 5.38 10.75 2 4.75 9.50 2 3.50 7.00 26 2.13 55.25 You are working with whole feet units of measure on the pipe at ten feet. Your cut lengths, however, are a much finer resolution at what appears to be 1/4 inch. So my suggestion would be to make the sorting program and be sure to keep whatever scraps you get for future jobs. You can even keep a scrap inventory right in the sheet (and truck). Seems like a variation of a "bubble sorter", which is an old computer programming term for a specific type of sorting method. There can be a goal of targeting minimizing losses on all pipes, OR you could stop at a given minimum and keep that scrap piece for another time, and use another pipe length for the next set of cuts. The goal of that additional parameter would be so that more often than not, your scrap remainders will be usable lengths, whereas if you cut leaving only an inch or whatnot, you remainders will truly be lost scrap. The other thing is the mentality of the cutter. If you optimize this process, they have to understand not to simply cut 26" segments until they get 12 pcs cut (as an example), they have to follow a sorted cut sheet and go through and mark all the uncut pipe first, which causes a lot less mistakes than trying to do it right there at the cut-off saw. Another way is to make an av\ctual pipe list where each pipe is called out, not simply "ten of these". So 50 pipes would be a 50 line cut list, and the print out product would be the sorted cut sheet for whatever base length you have (presumably other base lengths could be entered). This will require VB for the sorting list from what I can tell. some genius could write function code to do it with some temp tables, etc, and get you there without a macro, but not easily. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating a cut list
wabbleknee wrote on 23/01/2013 :
I have to cut PVC pipe for various jobs. These PVC pipes come in a 10 ft length. I am trying to maximize the cuts and generate less scrap. i.e. The first column "A" is Qty, "B" is length (inches) and "C" is sum. Can I put this in a look up table?? and have it pick lengths "B" not to exceed 10 feet (120 inches) and continue on until all lengths are satisfied? This particular job requires 5 10 ft pieces, with about 5 feet scrap. A B C 2 26.00 52.00 3 24.00 72.00 1 22.75 22.75 2 22.00 44.00 2 21.25 42.50 2 20.00 40.00 4 13.00 52.00 1 11.75 11.75 2 11.00 22.00 2 10.00 20.00 1 9.75 9.75 2 9.00 18.00 4 7.75 31.00 2 6.50 13.00 2 5.50 11.00 2 5.38 10.75 2 4.75 9.50 2 3.50 7.00 26 2.13 55.25 What needs to be allowed for the width of the saw? -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating a cut list
On Wed, 23 Jan 2013 22:49:40 -0500, GS wrote:
wabbleknee wrote on 23/01/2013 : I have to cut PVC pipe for various jobs. These PVC pipes come in a 10 ft length. I am trying to maximize the cuts and generate less scrap. i.e. The first column "A" is Qty, "B" is length (inches) and "C" is sum. Can I put this in a look up table?? and have it pick lengths "B" not to exceed 10 feet (120 inches) and continue on until all lengths are satisfied? This particular job requires 5 10 ft pieces, with about 5 feet scrap. A B C 2 26.00 52.00 3 24.00 72.00 1 22.75 22.75 2 22.00 44.00 2 21.25 42.50 2 20.00 40.00 4 13.00 52.00 1 11.75 11.75 2 11.00 22.00 2 10.00 20.00 1 9.75 9.75 2 9.00 18.00 4 7.75 31.00 2 6.50 13.00 2 5.50 11.00 2 5.38 10.75 2 4.75 9.50 2 3.50 7.00 26 2.13 55.25 What needs to be allowed for the width of the saw? The cut-off saws most often in use would have a 1/8 or 5/32 saw kerf. I would make a system where that is a stored, user selected value (as in a cell assigned a range name) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating a cut list
CellShocked presented the following explanation :
The cut-off saws most often in use would have a 1/8 or 5/32 saw kerf. I would make a system where that is a stored, user selected value (as in a cell assigned a range name) I agree! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating a cut list
On Thu, 24 Jan 2013 02:04:43 -0500, GS wrote:
CellShocked presented the following explanation : The cut-off saws most often in use would have a 1/8 or 5/32 saw kerf. I would make a system where that is a stored, user selected value (as in a cell assigned a range name) I agree! I started a workbook for him, but it may not be the direction he wants to go. I think each pipe should get a listing, just like a military wire cut list. So the 2 or 3 or whatever @ whatever length listing would have each qty item broken out. Give it a look... http://www.mediafire.com/view/?kpf1sp8eavakmpb |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating a cut list
CellShocked wrote :
On Thu, 24 Jan 2013 02:04:43 -0500, GS wrote: CellShocked presented the following explanation : The cut-off saws most often in use would have a 1/8 or 5/32 saw kerf. I would make a system where that is a stored, user selected value (as in a cell assigned a range name) I agree! I started a workbook for him, but it may not be the direction he wants to go. I think each pipe should get a listing, just like a military wire cut list. So the 2 or 3 or whatever @ whatever length listing would have each qty item broken out. Give it a look... http://www.mediafire.com/view/?kpf1sp8eavakmpb That's a pretty straight forward design that should work. I already have a working solution for a material cut list (which also does costing) that I did for a client. This was custom designed to service "machining allowance" where the material was used on a particular cnc machining center, and is tied into their jobcosting tables. Unfortunately it's proprietary and so I can't share it. The most important things about this type of project is to work with the "yield" number of pieces. This includes tracking the shorts (leftovers), and correctly allowing for "kerf" of the workstation (saw machine) used for cutting the material, as well as any machining allowance required for followup processes. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating a cut list
"GS" wrote in message ... CellShocked wrote : On Thu, 24 Jan 2013 02:04:43 -0500, GS wrote: CellShocked presented the following explanation : The cut-off saws most often in use would have a 1/8 or 5/32 saw kerf. I would make a system where that is a stored, user selected value (as in a cell assigned a range name) I agree! I started a workbook for him, but it may not be the direction he wants to go. I think each pipe should get a listing, just like a military wire cut list. So the 2 or 3 or whatever @ whatever length listing would have each qty item broken out. Give it a look... http://www.mediafire.com/view/?kpf1sp8eavakmpb That's a pretty straight forward design that should work. I already have a working solution for a material cut list (which also does costing) that I did for a client. This was custom designed to service "machining allowance" where the material was used on a particular cnc machining center, and is tied into their jobcosting tables. Unfortunately it's proprietary and so I can't share it. The most important things about this type of project is to work with the "yield" number of pieces. This includes tracking the shorts (leftovers), and correctly allowing for "kerf" of the workstation (saw machine) used for cutting the material, as well as any machining allowance required for followup processes. -- Garry Thanks guys for the suggestions. Yes, the most recent blade in the saw is 1/8". You might think for a moment that this would be negligible, however, just last week a particular list required 3.97 10' lengths. As careful as I was, I had to cut into a 5th length due to blade width. I have been doing it the manual way now for quite a while and most of the smaller scraps I have been cutting into 2 1/8" to join various elbows, tee's etc. (1.25" pipe) I am experimenting now with a spreadsheet that lists all lengths, 1/8" increment, and putting a 1 in the pick column I need, it will pick that length, put it in another column that I sum. Conditional format that sum column so that it turns red when over 120" OR I can also use the sum figure to subtract from 120 to give me amount remaining or even a PIE chart like Hard drive properties show :o) i.e. A B C D 1 12 12 48 24 0 1 36 36 D1 is summing cell =sum(C:C) and will read 48 in this example and continue on until full utilization of that 10 foot pipe Formula for C1 (& down) = IF(A1=1,B1,"") If there is a "1" in column A1, write the contents of B1 into C1. This works but there is a lot of selecting and unslecting in the maximizing process. Pipe #2, I would enter 2 and have a separate summing column and so on. Sort the whole mess by the pick column "A" for cut list. Just thought I could "automate" this process somehow because there is always "changes" to the job and screws up everything :o) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating a cut list
wabbleknee explained on 24/01/2013 :
Just thought I could "automate" this process somehow because there is always "changes" to the job and screws up everything You can only do so much as you prepare your solution to do. For example, the solution I did for my client will auto-update the cut list for changes made in the job run. Since we're dealing with product manufacturing, the cut list determines how much raw stock is required to make the qty of the run for each item. It also determines how shorts are handled based on the final size of the short. If there's no existing items that can use the short then it gets charged to the run as a consumable, and the cost of material per item is adjusted for that. On top of that, any scrap/spoils are also accounted for in the costing based on what step of the run they are scrapped. This only reflects the cost of the run, not the cost of the items being run. As long as you have an efficient mechanism for managing shorts, you can easily optimize your raw material usage to include the shorts when calcing how many full-length pieces are needed. A run requiring 3.5 lengths should pull the .5 from shorts if possible, otherwise a new short will result from pulling/ordering 4 lengths for the job. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating a cut list
On Thu, 24 Jan 2013 16:28:21 -0500, "wabbleknee"
wrote: Thanks guys for the suggestions. Yes, the most recent blade in the saw is 1/8". You might think for a moment that this would be negligible, however, just last week a particular list required 3.97 10' lengths. As careful as I was, I had to cut into a 5th length due to blade width. I have been doing it the manual way now for quite a while and most of the smaller scraps I have been cutting into 2 1/8" to join various elbows, tee's etc. (1.25" pipe) I am experimenting now with a spreadsheet that lists all lengths, 1/8" increment, and putting a 1 in the pick column I need, it will pick that length, put it in another column that I sum. Conditional format that sum column so that it turns red when over 120" OR I can also use the sum figure to subtract from 120 to give me amount remaining or even a PIE chart like Hard drive properties show :o) i.e. A B C D 1 12 12 48 24 0 1 36 36 D1 is summing cell =sum(C:C) and will read 48 in this example and continue on until full utilization of that 10 foot pipe Formula for C1 (& down) = IF(A1=1,B1,"") If there is a "1" in column A1, write the contents of B1 into C1. This works but there is a lot of selecting and unslecting in the maximizing process. Pipe #2, I would enter 2 and have a separate summing column and so on. Sort the whole mess by the pick column "A" for cut list. Just thought I could "automate" this process somehow because there is always "changes" to the job and screws up everything :o) Did you look at the workbook I made for you? I posted a link in the post you responded to. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating a cut list
"CellShocked" wrote in message ... On Thu, 24 Jan 2013 16:28:21 -0500, "wabbleknee" wrote: Thanks guys for the suggestions. Yes, the most recent blade in the saw is 1/8". You might think for a moment that this would be negligible, however, just last week a particular list required 3.97 10' lengths. As careful as I was, I had to cut into a 5th length due to blade width. I have been doing it the manual way now for quite a while and most of the smaller scraps I have been cutting into 2 1/8" to join various elbows, tee's etc. (1.25" pipe) I am experimenting now with a spreadsheet that lists all lengths, 1/8" increment, and putting a 1 in the pick column I need, it will pick that length, put it in another column that I sum. Conditional format that sum column so that it turns red when over 120" OR I can also use the sum figure to subtract from 120 to give me amount remaining or even a PIE chart like Hard drive properties show :o) i.e. A B C D 1 12 12 48 24 0 1 36 36 D1 is summing cell =sum(C:C) and will read 48 in this example and continue on until full utilization of that 10 foot pipe Formula for C1 (& down) = IF(A1=1,B1,"") If there is a "1" in column A1, write the contents of B1 into C1. This works but there is a lot of selecting and unslecting in the maximizing process. Pipe #2, I would enter 2 and have a separate summing column and so on. Sort the whole mess by the pick column "A" for cut list. Just thought I could "automate" this process somehow because there is always "changes" to the job and screws up everything :o) Did you look at the workbook I made for you? I posted a link in the post you responded to. Yes I did, thank you very much. Should have said thanks long time ago. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generating a cut list
On Sat, 26 Jan 2013 15:29:45 -0500, "wabbleknee"
wrote: "CellShocked" wrote in message .. . On Thu, 24 Jan 2013 16:28:21 -0500, "wabbleknee" wrote: Thanks guys for the suggestions. Yes, the most recent blade in the saw is 1/8". You might think for a moment that this would be negligible, however, just last week a particular list required 3.97 10' lengths. As careful as I was, I had to cut into a 5th length due to blade width. I have been doing it the manual way now for quite a while and most of the smaller scraps I have been cutting into 2 1/8" to join various elbows, tee's etc. (1.25" pipe) I am experimenting now with a spreadsheet that lists all lengths, 1/8" increment, and putting a 1 in the pick column I need, it will pick that length, put it in another column that I sum. Conditional format that sum column so that it turns red when over 120" OR I can also use the sum figure to subtract from 120 to give me amount remaining or even a PIE chart like Hard drive properties show :o) i.e. A B C D 1 12 12 48 24 0 1 36 36 D1 is summing cell =sum(C:C) and will read 48 in this example and continue on until full utilization of that 10 foot pipe Formula for C1 (& down) = IF(A1=1,B1,"") If there is a "1" in column A1, write the contents of B1 into C1. This works but there is a lot of selecting and unslecting in the maximizing process. Pipe #2, I would enter 2 and have a separate summing column and so on. Sort the whole mess by the pick column "A" for cut list. Just thought I could "automate" this process somehow because there is always "changes" to the job and screws up everything :o) Did you look at the workbook I made for you? I posted a link in the post you responded to. Yes I did, thank you very much. Should have said thanks long time ago. Worst case, you can cut and paste some of the comments and save some spiffing-up time. :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generating a list | Excel Worksheet Functions | |||
Generating a List | Excel Programming | |||
Generating a list | Excel Worksheet Functions | |||
Generating list | Excel Worksheet Functions | |||
list generating | Excel Worksheet Functions |