Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
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 |