sergiu_ciuhnenco Posted January 29, 2015 Share Posted January 29, 2015 I have a global question for excell Gurus , somebody have any IDEEA how is possible to optimize the lenght of rebar for a least waste ? In my country the standard lenght is 12 meters For Example I have : 3 position Ø 16 lenght 4 m , 3 position Ø 16 lenght 5 m , 3 position Ø 16 lenght 3 m the best way it will be to cut from 12 meters 5+4+3 But somebody at building site can cut onother way , which will be uneconomic. I attach a workbook. is impossible every time create from bombinations 12 meters lenght but is important to get less waste. I posted this question also on excel forum , may here is other Ideas I don't to ofend other guys but I think here are the better mind Experiment.xlsx Experiment.dwg Quote Link to comment Share on other sites More sharing options...
BIGAL Posted January 29, 2015 Share Posted January 29, 2015 Fuzzy logic comes to mind If you draw all the bars and shuffle adding them up and down adding them together until they fit in a 12m length how many times up and down ? There is software out there that does stuff like this fitting shapes into a rectangle similar approach. Keep repeat till leftover is minimal. Quote Link to comment Share on other sites More sharing options...
SEANT Posted January 29, 2015 Share Posted January 29, 2015 I'm probably reading things wrong but: Is there a coordination break - between drawing and spreadsheet - based on bar position and bar length? The break seems to start somewhere between position 35 (35 or lower all correct) and position 38 (here or higher, doesn't match between files). Quote Link to comment Share on other sites More sharing options...
sergiu_ciuhnenco Posted January 29, 2015 Author Share Posted January 29, 2015 Yes the documents are different, I attach them ,may be somebody have a idea with lisp , may be somebody with macro excel ... it doesn't matter in which program it will be done , important ,it should working Quote Link to comment Share on other sites More sharing options...
ymg3 Posted January 29, 2015 Share Posted January 29, 2015 (edited) sergiu, Your are after the 1d Cutting stock problem. Here is a link to a page where you can DL an executable that will do it. http://delphiforfun.org/programs/Cutting%20Stock.htm ymg Edited January 29, 2015 by ymg3 Quote Link to comment Share on other sites More sharing options...
lrm Posted January 30, 2015 Share Posted January 30, 2015 If you want to use Excel to solve this optimization problem you can use the Solver feature. Here I have set up your worksheet to show how to handle the problem for the 3 bar lengths of 25 DIA. The first step is to determine possible ways that the 3 lengths can be obtained from a single 12000 length bar and the amount of waste. Cases A - F show 6 ways and the amount of waste for each. I started with an initial guess of 10 units for each of the 5 cases. The table calculates the waste per bar in column L. Column P shows the total waste (waster per bar times number of bars used). Cell P73 also adds the waste as a result of any extra bars produced beyond what is needed. You then set up the conditions using Solver. The objective of the task is to minimize the waste so the Objective cell is the total waste, cell P73. The number of each case used is defined in cells F65:F70, with the constraints as noted specifying that the number of each size must be greater than or equal to the number needed. Clicking Solve yields the following. Here's a link for the Excel file with my edits: https://dl.dropboxusercontent.com/u/18135203/Experiment-lrm.xlsx What version of Excel are you using? ~ lrm Quote Link to comment Share on other sites More sharing options...
ymg3 Posted January 30, 2015 Share Posted January 30, 2015 Irm, This is certainly a correct solution to the problem at hand. However, using Excel solver you have to setup the patterns of cut manually before proceeding. As the number of required length increases the number of possible patterns out of a bar grows exponentially and make the method impractical. ymg Quote Link to comment Share on other sites More sharing options...
sergiu_ciuhnenco Posted January 30, 2015 Author Share Posted January 30, 2015 I am using 2010 version . Here is one advice : I need to make a guess for an answer as a start.If your guess is wrong then you make another guess, until/if you can come up with a formula. Click the button and watch it guess your answer 20 times. It will copy to the range on the right the best guess. I make little changes in macro and it works but , but does not work for more rebars If some one have some Ideeas how it possible to make it universal , it will be supper ! Experiment 2.zip Quote Link to comment Share on other sites More sharing options...
lrm Posted January 30, 2015 Share Posted January 30, 2015 Solver uses guesses to find a solution. You can automate the process of generating the possible cases for needed possible bar lengths. I am traveling so don't have time for a detailed answer. Good luck. Quote Link to comment Share on other sites More sharing options...
ymg3 Posted February 17, 2015 Share Posted February 17, 2015 sergiu, I have posted at Theswamp an autolisp solution to your problem. Here is the link: The Cutting Stock Problem ymg Quote Link to comment Share on other sites More sharing options...
sergiu_ciuhnenco Posted February 18, 2015 Author Share Posted February 18, 2015 Hi ymg3 !!! Sorry but I can't undestand how it works ? I saw a lisp file, I put it in mine attached file , I can not understand the meaning of the results? Quote Link to comment Share on other sites More sharing options...
tazzzz Posted February 19, 2015 Share Posted February 19, 2015 Hello all, Interesting topic. A simple question: is this algorithm counting for splices ? The more splices you create the more extra material you add. Thanks. Quote Link to comment Share on other sites More sharing options...
ymg3 Posted February 19, 2015 Share Posted February 19, 2015 tazzzz, Afraid not, what it does is to minimize the amount of waste when cutting the bars. It does also attempt to minimize the number of patterns utilized to reach a minimum of waste. ymg Quote Link to comment Share on other sites More sharing options...
ymg3 Posted February 19, 2015 Share Posted February 19, 2015 sergiu, I've updated the lisp so that it might be easier to understand the results. ymg 1DCSP.LSP Quote Link to comment Share on other sites More sharing options...
sergiu_ciuhnenco Posted February 20, 2015 Author Share Posted February 20, 2015 Ymg , the third problem is what a need ,I understood it , I change some values manualy and it work realy fine, now the big problem is how to introduce them automaticaly in lisp file,it gona take a long way to do it manualy , first I need to export and sort them in excell after that to copy them to lisp file . One more iDea it will be cool if every problem will reprezent a diameter for example: Problem 1 :∅10; Problem 2 :∅12; Problem 3 :∅14;... ∅16;∅18;∅20;∅22;∅25; ∅28; Problem 10 :∅32; Quote Link to comment Share on other sites More sharing options...
ymg3 Posted February 20, 2015 Share Posted February 20, 2015 sergiu, As it is the program is only a proof of concept. It would need work on the I/O so that we can enter a file and subdivide it on the size of the bar. Then the output could be an autocad table or a graphical view. I don't know where is your original data. If it is in excel or in a csv or txt file, would be simple to import it. Post a real sample of your list and then I'll see. ymg Quote Link to comment Share on other sites More sharing options...
sergiu_ciuhnenco Posted February 20, 2015 Author Share Posted February 20, 2015 (edited) The main data is in autocad; There are blocks with attributes , I export them in Excel and already there I make the specification. File with data is atached in first page "experiment" Sorry I sort them inversely (mean the lenght) ... Experiment.xlsx Edited February 20, 2015 by sergiu_ciuhnenco Quote Link to comment Share on other sites More sharing options...
ymg3 Posted February 20, 2015 Share Posted February 20, 2015 sergiu, Here I've modified the lisp so it will read your file if you save it as csv. Don't want to mess with direct from excel as there are too many versions. The Output is still crude and to the text screen. To Run issue the command "SERGIU" ymg 1DCSP.LSP Experiment.csv Quote Link to comment Share on other sites More sharing options...
sergiu_ciuhnenco Posted February 21, 2015 Author Share Posted February 21, 2015 Command " sergiu " : funny !!! "The Output is still crude , in to the text screen" That's right , it gonna be more useful in table format, but is enough for you , you really work to much !!! Phanks a lot , you help the economy of Moldova and not only Moldova I think !!! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.