[SOLVED] How to optimize cost and material with formula, Solver or any method

Just checking I understand you problem properly. In your uploaded file order 1001 has a solution where you use 1 box 14X14X8 and 1 box 13X9X9 but if you used a 14X14X14 box that would be a cheaper solution for packing costs but this box would have 3 "empty" spaces as this is a 36 unit and you only need 33.

So is your problem really that all boxes should be filled to maximum capacity and the number of boxes selected and their size should be the cheapest possible combination?

Alf

ALF,

Thanks for the help.

Yes. I need the cheapest packing material with combination of different items. The sample data was only for demonstration purpose. The true data will be different. I just need to know how to do it and I will replace with the true data.

Thank in advance for the help.

Jackson

Ok I've set up a solver model based on your uploaded file. To test you need to run the macro "Solver_loop". Before you can run this macro you must set a reference to solver (I assune solver is installed on your PC).

To set a reference to Solver click on "Developers" tap, then click on the "Visual Basic" icon and select "Tools" -> "References" and tick the box marked "Solver"

I've split you apple and orange to separate sheet one for apple and one for oranges.

Macro starts by clearin all data on these two sheets. Then it aplies autofilter to sheet1 in order to separate apples and oranges and result are copied to the appropriate sheet.

At the moment solver is set to match the number of boxes with the number of cans this is set by the line for apples

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

for oranges.

Below these lines are a green line i.e. not active at the moment. Difference is that the "Relation:=3" this part canges the setting from "equal to" to "equal or grater than".

I've tested the effect of this change and you will find the result on sheet2.

Alf

Alf,

First of all, I need to thank you for all of your help. I had learned a lot in this thread.

I majored in political science but end up getting a job in a different field. I need to use a lot of Excel at work. It seems everyone at work knows a lot of Excel, I am trying to keep up with them.

I just did all as you stated in the thread. I am using my wife's computer, now I see the effect. Thanks again for the explanation.

One thing to ask is if I have new data, which tab should I enter the data to? What I mean is which sheet content the source data?

Thank you very much again for the help.

Jackson

Hi Jackson
It works now, macro and all?

For new data you enter it in sheet1 in the same place as the old data was. If new data is more or less row than the old data this is no problem as macroc checks the number of rows.

When I get a bit more time i.e. tomorrow I can upload a world document explaning in detail how this macro works as this will give you a better understanding of a macros strength and weknes.

Alf

Alf,

My goodness. I just tested by adding items in the sheet 1. It all works out. Thank you very very much.

I cannot say enough thank you for all you did.

Looking forward for the word. I will study back and forth to learn it. Thanks AGAIN.

Have a nice weekend.

Hi Jackson
Thanks for rep an wishes for nice weekend. Have put together a walk-through for the macro. Will not be a bestseller but hopefully you will find it useful.

To see what happens you could when you run the macro in "slow motion" i.e. go to the developers tap. Click on the macro icon and when the the "Macro" windows opends click on "Step Into" you will be go to thr macro and by pressing the "F8" button on the key board the macro will be executed one line at the time.
By jumping between the Visual Basic window and the "excel file" view you can see what happens, remember what should be done will only happens after you move on to the next line.

More possible uses for a setup like this, perhaps running previous months on this file and see what solver says is the best solution then compare this with what really happened?

Or do a forecast on the future based on expected sales and average customer demands, or change size om some boxes and see if this may bring down costs?
The Excel solver is a quite powerful tool for these kinds of studies.

A nice weekend to you as well.

Alf

Ps if this solves you problem don't forget to mark thread "Solved". Go to first post, click "Thread Tools" and select "Solved" from dropp-dowe.

Alf,

This is Thanksgiving weekend in the U.S. As of today, you have giving me one more thing to be thankful. Thanks for the kind action. I will study the word doc, and will do some testing with really data when I go back to work on Monday. I will post the result here.

Thanks again for the great help.

Jackson

You are welcome.

Alf