Looping a rotation into Excel cells

Question

I have a database of users (with a number assignment) and an Excel listing of items that I am successfully pulling into two arrays. One array of users, the other of items from Excel (tasks). I am trying to think of the logic to represent this scenario:

A user in the DB can exist with a number 0-5. Basically this represents how many days off they are in the week, and help break up how many items in the Excel range each person can get so as to proportion correctly items (before I was using a Boolean indicator to indicate to either include or exclude). For example:

User   |  Present #
-------------------
Jared  |  0 'present daily
John   |  0 'present daily
Mary   |  1 'off 1 day
Tom    |  5 'off rotation entirely

Question is: what is the best way to relate this to how many items they should be getting overall?

I would expect Jared and John to get the most, Mary a bit less, and Tom would never be included. Let's say I have 50 items.

One way I have thought is, while looping the names into Excel, count each time I start back at the top of the array as a "pass" (while assigning into Excel cells).

    Anyone with a 0 is never skipped through each pass
    Anyone with a 1 is skipped every 4th pass
    Anyone with a 2 is skipped every 3rd pass
    Anyone with a 3 is skipped every 2nd pass
    Anyone with a 4 is skipped every other pass
    Anyone with a 5 is never included (easy)

For my example, Jared and John would always be used, Mary would be skipped every fourth pass, and Tom would never be used.

Does this make sense?
What is the best way to catch looping through an array every Nth time?
Am I going about this in the correct manner?

pnuts · Accepted Answer

To avoid a lot of looping and the delays this might cause, I’d suggest calculating a ‘demand factor’.
There are 50 items (in the example) to be distributed according to availabilities. The availabilities are shown as 0 present daily to 5 off rotation entirely, but it is easier to work with these the other way around: ‘off rotation’ has no resources available so 0 and ‘presently daily’ has all weekdays (?) available, so 5.

The User | Present # table would then become:

 
Jared   5
John    5
Mary    4
Tom     0
      14

So 14 person-days are available to cover 50 items, an average of 3.57 items per person-day. Presuming an item can’t be split that is 3 items per person-day and 8 over. The ‘3 each’ can be allocated in one pass by multiplying the (revised) table values by INT(item_total/table-total). So for Jared and John the result is 5x3 = 15 and for Mary 4x3 = 12.

That though only accounts for 42, so 8 have yet to be allocated. 3,3,2 ‘extras’ is obvious (resulting in 18,18,14) but programming that not so easy. I’d suggest where there is any residual from the INT formula then use its result +1 (ie here 4 rather than 3) accept preliminary results of 20,20,16,0 (6 too many) then loop through each user knocking 1 off (where possible) until 6 have been knocked off.

Looping a rotation into Excel cells

Answers (2)

Related Questions