Forum Discussion
Divide list of volunteers into vaccination shifts
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.
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.
thank You for the mega formula, it is a very good trick, how to reference the weekday from one table to another. And I have learned one more thing - the XLOOKUP returns internally a range (I could not understand the integer output of the XLOOKUP used in the COLUMN formula ...
... but the "Evaluate formula" function shows it
Zdenek
