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)

I assume i will need to use a different function to the randbetween function.

Many thanks for your help in advance.

>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

traineeross <traineer...@discussions.microsoft.com> wrote...
....
>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.
....
>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)
....

So there are only 6 people, and there are 7 days in a week, and only
one shift per week and at least one shift every two weeks. So there
are fewer than 6 shifts per week but at least 6 shifts every two weeks
to which to assign staff?

If there were 5 shifts per week, simply including the first shift of
the second week in the single shift logic for the first week would
ensure the employee who didn't have a shift in the first week would
have the first shift of the second week. Then start the logic over.

Put the employee names in a contiguous, single-column range named
Staff. Then if the first shift assignment would be in, say, cell D3,
and the second shift assignment in cell D4, etc., try these formulas.

D3:
=INDEX(Staff,INT(1+6*RAND()))

D4 [array formula]:
=INDEX(Staff,SMALL(IF(COUNTIF(D$33,Staff)=0,
ROW(Staff)-MIN(ROW(Staff))+1),INT(1+(6-COUNTA(D$33))*RAND())))

Fill D4 down into D58. Select D48, run Edit > Replace, and replace
$ with nothing to remove the absolute references. Then copy D38 and
paste it into D914. Then copy D4 and paste it into D9 to ensure that
the employee with the 6th shift doesn't get the 7th shift too. Then
copy D914 and repeatedly paste into further cells below D14.