Automatically divide list into even segments

**ffmariners** · 01-28-2010, 08:27 PM

Here's the situation:
I work in a warehouse and we have to divide work out between union employees. With that being said, the work load needs to be relatively even.

My supervisor usually takes the workload and manually types it into an excel list in the format:

1st column (A2-A10) is truck number, and
2nd column (B2-B10) is amount of work.

He then created 4 lists (4 different teams of workers) in columns G-H, I-J, K-L, and M-N with data validation in the 1st column (same format, truck number). The 2nd column is then vlookup'd to show the work load. The total work load sums up at the bottom. For each team he can sit there and manually assign each truck to each team using the drop boxes to get the work load relatively even.

He gets the data from our warehouse management system, so to help him save some time I decided to make an "import" tab where you just copy/paste the CSV export from the WMS. Then, the original list he used to type in (Columns A/B) feeds right over. The 1st column (truck #) is just a simple =Import!B2 and so on. The second column is then a vlookup to get the work load from the Import tab.

However, he still has to sit there and manually assign each truck to each team to get it relatively even.

Is there some kind of cool excel coding that could actually attempt to create equal segments out of a group of numbers? Maybe wishful thinking.

Attached a copy.

**shg** · 01-28-2010, 09:38 PM

Maybe as attached.

**martindwilson** · 01-28-2010, 10:04 PM

perhaps something like this with helper cols on data sheet( ive been working on that for an hour lol)

**ffmariners** · 01-28-2010, 11:12 PM

Originally Posted by shg

Maybe as attached.

I like it. It's hit or miss. Sometimes it does it very evenly, sometimes it is a bit off.

What's the logic behind it? What functions?

**ffmariners** · 01-28-2010, 11:13 PM

Originally Posted by martindwilson

perhaps something like this with helper cols on data sheet( ive been working on that for an hour lol)

Nice, that is definitely an easier way to manually do it. A lot more intuitive... keyboard is def faster than mouse

(in regards to typing team number instead of selecting from a drop box)

Thanks.. I will incorporate this idea into what he currently uses. Still going to mess around with the automatic one I got above.

**shg** · 01-29-2010, 12:23 AM

The logic is simple; it assigns the first N trucks in rotation, and assigns each subsequent truck to the team that has the least work in the prior round. It works best if you sort the trucks descending by work, so you assign the largest first.