Forum Discussion
Divide list of volunteers into vaccination shifts
Imagine, that you get list of volunteers, which will help in your vaccination centre. The centre is opened on certain weekdays, morning or afternoon or both.
Your task is to divide the volunteers into the vaccionations shifts - every shift must be ensured by two volunteers. The volunteers have limitations (somebody can work only morning shift, somebody cannot come on Friday etc.)
Now we do it all manually - get one day, get one shift and fill with volunteers by checking their limitations. Get next shift and select next volunteers. Once you reach the end of the volunteers list, go to the beginning and start over, to fulfil all shifts.
See attached example.
Is it a task for Power Query or is it too much logic and I should use VBA?
Thank You
Zdenek Moravec
Cesky Krumlov, Czech Republic
Totally forgot to plug that piece in! Made the filter a bit more complicated, but using this worked instead. Basically just looks at which day of the schedule it is, does an xlookup to find which column to filter by (by day), turns whichever cell the day (1-7) is in, into a column reference E:E, then filters that for "Yes".
+TRANSPOSE(IF(Q5="Morning",FILTER(A:A,(B:B="yes")*(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(XLOOKUP(R5,$E$2:$K$2,$E$2:$K$2)),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(XLOOKUP(R5,$E$2:$K$2,$E$2:$K$2)),4),"1",""))="Yes")),FILTER(A:A,(C:C="Yes")*(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(XLOOKUP(R5,$E$2:$K$2,$E$2:$K$2)),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(XLOOKUP(R5,$E$2:$K$2,$E$2:$K$2)),4),"1",""))="Yes"))))Take a look now, runs a little slower but it works, I would only have the random numbers generate once when needed rather than on cell updates.
4 Replies
I came up with a solution that uses random numbers to generate a list based on the specified criteria, should be scalable to fit any number of volunteers. Let me know what you think!
I changed the input table a little bit, now the formulas use a "Yes" for mornings and afternoons to indicate availability, rather than morning/afternoon/both. Its broken down by weekday as well instead of a flat "cant work tuesday", you input their availability throughout the week with Yes and No.
The right schedule is mostly the same, the grey columns are just helper columns and can be hidden.
For each shift (weekday and time of day), it generates a list of people that can work based on the criteria set in the left table, and from there, picks a random worker to work. It also looks to see if it is a two person shift, and avoids picking the same person for both shifts (looking to the backup choice if the first choice doesn't fit the bill). I think it accomplishes what you wanted!
- Hello DKoontz,
thank You very much, it looks really smart and simple. The volunteer setup table is all right in your form. I understand, that the FILTER returns an array of volunteers, RANDBETWEEN generates a pointer to this list and XLOOKUP finds the name for the pointer.
The grey column Day is probably prepared to crosscheck the weekday availability of the volunteer, how would you check, if this vac centre weekday matches with the volunteer weekday requirement? (the FILTER function checks now only morning/afternoon shift).
Thank You.Totally forgot to plug that piece in! Made the filter a bit more complicated, but using this worked instead. Basically just looks at which day of the schedule it is, does an xlookup to find which column to filter by (by day), turns whichever cell the day (1-7) is in, into a column reference E:E, then filters that for "Yes".
+TRANSPOSE(IF(Q5="Morning",FILTER(A:A,(B:B="yes")*(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(XLOOKUP(R5,$E$2:$K$2,$E$2:$K$2)),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(XLOOKUP(R5,$E$2:$K$2,$E$2:$K$2)),4),"1",""))="Yes")),FILTER(A:A,(C:C="Yes")*(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(XLOOKUP(R5,$E$2:$K$2,$E$2:$K$2)),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,COLUMN(XLOOKUP(R5,$E$2:$K$2,$E$2:$K$2)),4),"1",""))="Yes"))))Take a look now, runs a little slower but it works, I would only have the random numbers generate once when needed rather than on cell updates.
