Selective Load Balancing Macro

[Dissonant]

Queue-upload.xlsm

I'll do my best to describe exactly what I'd like to do. The way it's setup right now is very makeshift, and doesn't quite do exactly what I'd like.

You can see I have 15 machines listed, each with a number of processes running (Queue Column). The Queue's are pulled from text files that our program generates for each machine. (This is done pretty make shift as well). The file name on each computer is called qlen-'number.'txt. When the "Get Queue" macro is called it straight copies the file names down in B132-B146. Then the Queue number in C2-C16 is parsed from that (I'm sure there's a strictly VBA way to do this, but the way i'm doing it will work for now, this isn't a huge priority).

The 2 other macros you'll see in the sheet are NewItems, which pushes a text file to each machine with the corresponding contents of E2-E16. Aside from the sloppy code of repeating the same function over and over, this works perfectly and there's really no reason to mess with it at this time

The main thing I'm looking to do is to properly balance new orders over each machine that has a checkmark next to it. Right now if you scroll over to the w, x, y columns you'll see the sloppy way I handled this. Most of the time an order will be pushed to all machines, but sometimes I need to be selective. My biggest issue right now is to take that number from C21 and splitting it across all the machines so that once they're split, all of the queues are as equal as they can be. Sometimes there is a much larger disparity in the queue length of machines and an order will be small enough that it can't even out all the queues. In those instances, it just needs to be distributed as evenly as possible.

The last thing I'd like to have is fairly easy expandability. If machines need to be added to the sheet, it would be nice to not have to do a ton of recoding.

This could be a doozy or it may be quite simple. Any help that could be provided would be very much appreciated.

[Solus Rankin]

Dissonant

Not trying to hijack your post, but I'm a lean challenge professional and load leveling (for processes or resources) is what I do.

I'm not sure you'll be able to effectively level load your queues without process turn times and process batch sizes. Stacking the number of processes in the queue can actually decrease your process load leveling if there is a deviation in the variables I listed before. Does that make sense?

[Dissonant]

I'm pretty sure I understand what you're saying. In theory, each process (1 item in queue) is exactly the same run time on exactly the same type of machine. However, theory and practicality are 2 different beasts. There are a million factors that can cause one machine to run faster or slower or have higher success rates on any given day. The closest way we can balance loads is by evening the number in the queues.

[Solus Rankin]

So each process only produces a quantity of one and all processes are for the same part?

[Dissonant]

That's correct

[shg]

Why assign tasks to machine queues all at once, rather than assigning items sequentially as each machine approaches availability -- like the checkout queue at Frye's?

[Dissonant]

For what we're doing, it doesn't make sense to wait to queue. Orders need to be queued at the time they are received.

[Dissonant]

As you can see from the spreadsheet, this is currently being handled by a formula within the cells. I'm not opposed to having this as a macro, I'm just better at working with excel formulas than VBA.

[Dissonant]

One idea that I had, I can plot out the logic, I just don't know how to code it:

Read in Queue Values Range(C2:C16)
Save a copy of Queue Values Range
Read in the Order Quantity (C21)

While Order Quantity >0
Find Minimum Value in Queue Values +1
Order Quantity -1
Loop

(Incremented Queue Values) - (Copy of Original Queue Values) = Number added to each queue
Output Number added to each queue to D2:D16