Multiple, variable workloads for multiple workers

[qobodo]

Hi everyone,

My problem reads as follows: I'm working in a warehouse and there is a variable number of loads, each consisting of a variable number of cases, that have to be prepared by a variable number of orderpickers every day.

The goal is to distribute the loads to the orderpickers so that the total workload per orderpicker is as evenly distributed as possible, and that no one gets more than 1200 cases. (see attachment)


I tried to solve this problem with solver, but I couldn't figure out how to set the parameters.


Can anyone help me please? Thank you so much!

[shg]

Maybe like this.

I sorted the loads descending by weight, and assigned the first three loads sequentially. The formulas do the rest.

It's not necessarily optimum, but it's reasonably close. The logic is to assign the next load to the guy with the least load so far.

Please Login or Register  to view this content.

[qobodo]

Thanks SHG, I tried out your proposition and it surely works fine, although, as you said yourself, it's not an optimum. What's more, the source file, which contains the data of the loads, is generated every day form an Access application, so the formulas would get overwritten. Thus the process should be written in VBA?

[shg]

Post an example of the data as imported from Access.

[qobodo]

This is the brute export data

[shg]

Where's the weight in all of that?

[qobodo]

As you can see in the header-row, the 3 main variables are:
NUM OF PALS, which represents the number of palets thsat compose each load
NUM of DIFF SKU's, which is the number of different products the orderpicker will have to look for in the picking line,
CASES, the total number of cases the load is made of.

Now, the main weight is put on the number of cases that every worker has to pick. This is the only numeric constraint that is given, and should not exceed 1200 cases per worker.
The 2 other variables are less weighted, but there should be as much equity as possible between the oderpickers. I.e., it's not an optimal solution to have one worker preparing 1000 cases for 450 SKU's, on 50 palets, whereas another one has 1010 cases but only 200 SKU's, and on 20 palets.

Anyway the weight would be
1. CASES
2. NUM of DIFF SKU's
3. NUM OF PALS