Hi

While I fully understand what you are trying to achieve here, I do not agree
with your modus operandi.

Having worked shifts, and having planned shifts all my life, I have found
that a shift system rotating on a set pattern works best for all concerned.
Too often, when you try and accomodate the wishes of everybody, you end up
annoying everybody, and people start complaining about favouritism. That's
the one end of the stick.

The other side of the story is that computers are not human, and work to set
logic. For a macro to accomodate peoples wishes, which are not according to
a set pattern, is not easy.

I would therefore recommend a different approach, and that is to use a
rotaline system. You do however have a couple of problems, and I do not know
whether you are flexible about this or not.

If you could, it would be better for you to reduce your staff by 2, and
increase the shifts per person to 14 in 28 days.

Again, I do not know whether this is possible or not, nor whether you are
prepared to go this route. Why do I say this? At present you have 50
bodies, and you want each to work 13 shifts in 28 days. Therefore 650 shift
in 28 days. This means that on each of the 28 days you require 23.21428571
bodies on duty. That is really a tough call.

If you have 48 staff member, each working 14 shifts, you will have 24 on
duty every day, and that is an easy roster to work out (2 days on, 2 days
off). That in turn means that each worker gets the chance to work on each
day of the week, and you cannot get fairer than that!

I know I am not solving your initial question, but think about it!

Regards

ve_2nd_at. Randburg, Gauteng, South Africa


"dcleave" wrote:


Hi.
Thanks for your interest in my problem.

To clarify, all staff are entitled to request up to 5 shifts, which
will be honoured as long not too many request the same day.

In practical terms, this translates as each row having 5 fixed 'D's in
place, before the macro is run, which cannot be moved. These are spread
randomly across the 28 days.

Any help would be gratefully received. I am not looking for people to
write the complete macro for me (although that would be nice!), but to
give suggestions as to how I can get a macro to cluster the 'D's in
each row whilst still maintaining the same number of 'D's in each
column.

David


--
dcleave
------------------------------------------------------------------------
dcleave's Profile: http://www.excelforum.com/member.php...o&userid=20562
View this thread: http://www.excelforum.com/showthread...hreadid=393767