Rota Generator

[TomMartin123]

Hi this may have already been covered somewhere but i cannot find anything.

I am looking at automatically generating a rota based on some parameters.

i have a list of staff and i have a week template with all available shifts that can been filled randomly.

However staff work a different number of shifts each week which is also highlighted.

How can i auto populate this rota to make sure staff are only doing the required number of shifts per week?

I have had a go myself by making a random generator for the week but it just duplicates names too much and i don't know how to set limits on this.

I have attached what i have done so far.

In an ideal world i would like to do this over 2 weeks and give everyone a weekend off that's fair and even.

Thanks in advanced

[JeteMc]

Hello TomMartin123 and Welcome to Excel Forum.

However staff work a different number of shifts each week which is also highlighted.

If the number of shifts is supposed to be in column D on Sheet2, that column is blank except for the header.
It may be easier to help if we could see a manually typed rotation so that we can use that as a guide in proposing formulas/code to automate the process.
Let us know if you have any questions.

[TomMartin123]

apologies i have attached again with number of shifts entered in the column.

[TomMartin123]

also there is no rotation. All that is required is that staff work the number of shifts they are contracted for over the 7 day period not working more than 1 shift per day

[JeteMc]

Someone may be able to come up with a solution that is closer to what you are looking for.
On Sheet 2 add the following columns:
1. Random populated using: =RAND()
2. The columns with the number of days to be worked: =IF($D2<>F$1,"",SUMPRODUCT(($E$2:$E$26>$E2)*($D$2:$D$26=F$1))+1)
3. The Map Ref column: =INDEX(F$1:I$1,AGGREGATE(15,6,(COLUMN(F$1:I$1)-COLUMN(E$1))/(F2:I2<>""),1))&"-"&INDEX(F2:I2,AGGREGATE(15,6,(COLUMN(F$1:I$1)-COLUMN(E$1))/(F2:I2<>""),1))
On Sheet 1 the left table is manually populated such that the assignment arrangement may be changed without changing the random assignment of individuals.
The right table is populated using: =INDEX(Sheet2!$C$2:$C$26,MATCH(B3,Sheet2!$J$2:$J$26,0))
Note that cells highlighted in yellow indicate that assignment exceeds the number of days for that person because there are not enough people on the list.
Let us know if you have any questions.