Solon Aquila
New Member
- Joined
- Sep 24, 2011
- Messages
- 25
First and foremost: if this is not the right forum, please let me know.
Second: I'm doing this for use where I work, but it's for me and me only because I like playing around with Excel and I like efficiency. Also - none of my co-workers has expressed even curiosity about my spreadsheets.
Third: my apologies for the length. This is the end result of months of me messing around with excel. If I'd asked at each step, the posts would each be much smaller than this.
I currently work in a relatively menial position where I take a block of raw material (remnants, mis-cuts, and the like) and determine which of our smaller pieces I can make from it. The two biggest challenges I face are trying to figure out, from a huge list of dimensions, which smaller piece is the best to cut from the block and, well, a good bit of no-challenge boredom. So, I made a worksheet that has all of the piece sizes listed and I type in the block dimensions and, using conditional formatting, every piece that can be cut from it is highlighted green. I further split that sheet into material densities and sort the pieces by piece volume (L×W×H), so the biggest pieces are at the top. I can use this to guess-timate the best work piece for that block.
So far, so good.
Then, I started wondering if the cut patterns I choose are providing the best yield (Block size ÷ Total volume of cut work pieces). So I determined that I could use 6 tables to cover all Source vs Work Piece combinations and now I can type the block dimensions and the work piece dimensions and, thanks to formulas and conditional formatting, the cut pattern with the best yield shows green.
Still so far, so good. I'm making good choices and great cuts and our yield is up.
To choose the best work piece based on yield, I take 5 or 10 of the highest volume pieces and type each piece's dimensions into the cut pattern sheet and record the best yield.
Then I choose the one that provides the highest yield. So, a multi-step process.
I'm certain that I can turn this into a macro of some sort, but my macro experience is limited. I like to keep things simple so those that follow me won't have a hard time administering, and that means a lot of formulas and conditional formatting.
My 'intended function' is:
1 - User enters the source block size and material type.
2 - The macro goes down every row of the original parts matrix file looking for the following:
• Material Type Match
• Work piece dimensions are amenable to being cut from that block.
3 - On identifying a piece that CAN be cut, the macro does the following:
• Pretends to be my '6 tables' worksheet and identifies the yield of that piece (and, coincidentally, the best cut pattern).
• Saves that information (on another worksheet? No idea yet)
4 - Continues to the next line until all workable pieces have been identified.
5 - Looks at all of the yield values of the workable pieces and sorts the piece information by yield, descending.
I hope to add a function that chooses best of 'multiple identical yield pieces' based on 'largest piece of leftover', but that won't be critical.
This will give me the ability to take the top 5 or 10 yields, check our current stock of each, and make a decision that considers best yield and current need.
The problems with my current system are that one must open and use multiple sheets and it is VERY basic, so if the user doesn't enter Index, Width, and Length in the right places, it can miss some opportunities. That applies to both source block and work piece. A script that will check all combinations for each work piece would catch it regardless of the order in which you enter the values.
My plan is to map out a basic strategy, cut each intended function into workable blocks, and learn a lot more about Excel scripting than I ever knew.
If it turns out that trying to do all of it in one go is too tough, I may scale back for initial use, then upgrade as I learn.
My request is going to be for advice. I'm not asking anyone to write code for me. I did that ages ago and learned almost nothing from it.
OH, side note: I'm using Excel 2007 and saving as 2003 as that's what's in use where I work. Yay.
So, unless this is the wrong forum or this post violates any rules (I did check, I think I'm ok), I'll post some mini-sheets so you can see what I'm talking about.
Second: I'm doing this for use where I work, but it's for me and me only because I like playing around with Excel and I like efficiency. Also - none of my co-workers has expressed even curiosity about my spreadsheets.
Third: my apologies for the length. This is the end result of months of me messing around with excel. If I'd asked at each step, the posts would each be much smaller than this.
I currently work in a relatively menial position where I take a block of raw material (remnants, mis-cuts, and the like) and determine which of our smaller pieces I can make from it. The two biggest challenges I face are trying to figure out, from a huge list of dimensions, which smaller piece is the best to cut from the block and, well, a good bit of no-challenge boredom. So, I made a worksheet that has all of the piece sizes listed and I type in the block dimensions and, using conditional formatting, every piece that can be cut from it is highlighted green. I further split that sheet into material densities and sort the pieces by piece volume (L×W×H), so the biggest pieces are at the top. I can use this to guess-timate the best work piece for that block.
So far, so good.
Then, I started wondering if the cut patterns I choose are providing the best yield (Block size ÷ Total volume of cut work pieces). So I determined that I could use 6 tables to cover all Source vs Work Piece combinations and now I can type the block dimensions and the work piece dimensions and, thanks to formulas and conditional formatting, the cut pattern with the best yield shows green.
Still so far, so good. I'm making good choices and great cuts and our yield is up.
To choose the best work piece based on yield, I take 5 or 10 of the highest volume pieces and type each piece's dimensions into the cut pattern sheet and record the best yield.
Then I choose the one that provides the highest yield. So, a multi-step process.
I'm certain that I can turn this into a macro of some sort, but my macro experience is limited. I like to keep things simple so those that follow me won't have a hard time administering, and that means a lot of formulas and conditional formatting.
My 'intended function' is:
1 - User enters the source block size and material type.
2 - The macro goes down every row of the original parts matrix file looking for the following:
• Material Type Match
• Work piece dimensions are amenable to being cut from that block.
3 - On identifying a piece that CAN be cut, the macro does the following:
• Pretends to be my '6 tables' worksheet and identifies the yield of that piece (and, coincidentally, the best cut pattern).
• Saves that information (on another worksheet? No idea yet)
4 - Continues to the next line until all workable pieces have been identified.
5 - Looks at all of the yield values of the workable pieces and sorts the piece information by yield, descending.
I hope to add a function that chooses best of 'multiple identical yield pieces' based on 'largest piece of leftover', but that won't be critical.
This will give me the ability to take the top 5 or 10 yields, check our current stock of each, and make a decision that considers best yield and current need.
The problems with my current system are that one must open and use multiple sheets and it is VERY basic, so if the user doesn't enter Index, Width, and Length in the right places, it can miss some opportunities. That applies to both source block and work piece. A script that will check all combinations for each work piece would catch it regardless of the order in which you enter the values.
My plan is to map out a basic strategy, cut each intended function into workable blocks, and learn a lot more about Excel scripting than I ever knew.
If it turns out that trying to do all of it in one go is too tough, I may scale back for initial use, then upgrade as I learn.
My request is going to be for advice. I'm not asking anyone to write code for me. I did that ages ago and learned almost nothing from it.
OH, side note: I'm using Excel 2007 and saving as 2003 as that's what's in use where I work. Yay.
So, unless this is the wrong forum or this post violates any rules (I did check, I think I'm ok), I'll post some mini-sheets so you can see what I'm talking about.
