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

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.

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

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)

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

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

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

"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)

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

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.

"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.

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. :-)