Sort and Group orders by capacity.
Hello,
I would like to sort my excel sheet
in this exemple, i need to group them as a max capacity of 80 per day and a max of 10 orders per day.
i know that with this small exemple it is easy to manually do it. but i'm looking forward to do this on a couple thousand orders.
Thanks,
David
I think that you can do with with the Solver add in - please see the attachment.
You can calculate a minimum number of days by dividing you total order sizes by 80 - this gives 7 in this case.
Use formula like in the sheet for days 1 to 7.
I've included an image of the solver constraints. The sheet has a 7 day solution to the sample data.
Martin
Thanks alot.
I'm trying this at the moment,
Although, the solver don't let me add the integer constraints. (also what does it do?)
thanks
David
Int is one of the options for the constraints if you scroll down.
Solver is basically trying lots of options to find one that fits.
Now i get a box saying: too many variable cells.
David
Ok - maybe you are working on a larger data set that the one supplied?
Yes, this was only an exemple of what i want to accomplish.
i wish to integrate this on a scale of about 4000 to 10000 orders. with a date range of 253 days.
David
Do earlier orders take precedence over later orders?
I'm thinking that you might need some sort of 'greedy' algorithm which picks orders one day at a time and aims to prefer the biggest and smallest so that the two maxima are not exceeded. Once it finishes with one day it starts with the next. Do you intend to run this one a day to pick just the next days work?
This scheduling process would be only use 2-3 time max per year.
all orders are already in "bank" since they are not really orders.
lets see it like warehouses.
i know how many warehouse (orders) I can move per day (10) even if it has only 1 item in it
I know that depending on how many item there is in one warehouse it take more time so max (80)
of course ill be adding some special add on this but if i can get this going ill be able to set everything else.
david
Hmmm... Getting late here - I'll think about it....
Thanks,
David
There might be an approach which is based upon the factors of 80.
First, pick all orders with size 80.
Then pairs of orders of 40
Then groups of 4 orders of 20
Then groups of 5 orders of 16
Then pairs of orders which total 80
Then pairs of orders which total 40
Then pairs of orders which total 20
Then pairs of orders which total 16
Then groups of three orders which total 20 (40 and 80 have lots of combinations)
Then groups of three orders which total 16
What's left might be addressed by the solver approach or left as a start for the next run.
Well, this is basically what I am currently doing right now.
But doing so with all lines, take way to much time.
The numbers I mention are only for example purpose.
The max number of item per day I can do is 500. but on my database here i have 4000 line of "orders" and there is about 3800 lines that have less than 100 items.
This is why i need something to automate the process.
David.
This is automatable. I can try to but something together - what is the maximum size of an order and what is the real maximum number of orders per day?
Maximum items in one order is 1467. but i can manually place all orders over 300 (there is 7)
the max number of orders per day is 50
The max number of items per day is 350
thanks
David
Please have a look at the attached file.
I've gone for an approach were the min and max daily size is specified and random combinations are tried to see it they meet the criteria.
It's not a fast piece of code so it is best left for a while to crunch through the possibilities.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks