I am trying to create a rota for work.
I have used the randbetween function to create a number between 1-6, and
then used the choose function to use th erandom number to pick a name from
the list of staff in the choose function.
D E F G
Tuesday PM 4 Gareth
WednesdayAM 6 Carolyn
Thursday PM 2 Fraser
Friday AM 5 Tiago
column F function =RANDBETWEEN(1,6)
column G function
=CHOOSE(F1,"Dela","Fraser","Richard","Gareth","Tiago","Brad")
Is there a way in which i can control the number of times a persons name
appears per week and per fortnight. (ie not more than once a week, and
must
have at least one shift per fortnight)
This looked like an intriguing problem to try and implement with worksheet
functions (which is what your post seemed to indicate you want to do), so I
decided to take a stab at it... however, you didn't provide enough
information, though. For example, how many workdays are in your work week or
in a fortnight; and how many shifts per day are there. Anyway, I decided to
give you formulas that would take the six names you provided and randomize
them without repeats. If you need more than 6 shift assignments, just
duplicate the formulas to the right of the rightmost set of formulas to get
another random arrangement of the six names... do that as many times as
needed to cover all of the time slots you have to fill.
Before I give you the layout, I want to remind you that this is not the best
way to implement your request as the names will be re-mixed every time you
do almost anything on the worksheet (for example... enter a value, erase a
value, etc.). I think a macro controlled by a button would be a better way
to go, but you need to provide the missing information before it would be
practical to write one. Okay, with that said, do the following.
1. Place your six names in the L column from row 2 to row 7 (the order of
the names is unimportant).
2. Place the formula =1+INT(6*RAND()) in M1 and copy it across to Q1.
3. Place the formula =IF(M$1=ROW()-1,L$7,L2) in M2 and copy it down to
M6.
4. Place this formula in M7: =OFFSET(L1,M1,0,1,1)
5. Place the formula =IF(N$1=ROW()-1,M$6,M2) in N2 and copy it down to
N5.
6. Place this formula in N6: =OFFSET(M1,N1,0,1,1)
7. Place the formula =IF(O$1=ROW()-1,N$5,N2) in O2 and copy it down to
O4.
8. Place this formula in O5: =OFFSET(N1,O1,0,1,1)
9. Place the formula =IF(P$1=ROW()-1,O$4,O2) in P2 and copy it down to
P3.
10. Place this formula in P4: =OFFSET(O1,P1,0,1,1)
11. Place the formula =IF(Q$1=ROW()-1,P$3,P2) in Q2.
12. Place this formula in Q3: =OFFSET(P1,Q1,0,1,1)
13. Place this formula in R2: =Q2
14. Finally, assign the names to the column you want (here, I used column G
because your post seems to indicate that is where you will want them)...
G1: =M7
G2: =N6
G3: =O5
G4: =P4
G5: =Q3
G6: = R2
That's it... the names in column G will be in random order with no repeats.
As I said earlier, if you need more names, simply copy the range L1:R7 off
to the right somewhere (say, starting in S1) and pick up the end row cells
from each column with a number in row 1. Note, the grouping must start in
row 1 as there are a couple of absolute row references in the formulas.
Don't forget to write back with the answers to the above questions (and any
other information about your scheduling that might be important) so someone
here can look into developing a button activated macro.
Rick