Sort Employees in Roster

[daniel.mrc]

Hi so I need to do a roster for 9 shops in separate locations, and everyday there need to be 20 people working out of 42 employees.

What I would like to do is for every shop I want to roster individuals that live closer to the shop, then if there is no one close to the shop I want another random person to be allocated to that shop.

At the bottom of the excel sheet I want a total of individuals that are working that day in that particular shop. Then I want all the roster to firstly be displayed as something similar to Sheet 2 and then to be displayed something similar to Sheet 3

I know that this is quite complex but I think with your help I can manage.

PS. the "shops" that I am doing the roster for are food banks, and the "employees" are volunteers. WE are not getting paid to do this, but I want to make the process easier.

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

Note: As you are new I will add it for you.Please read forum rules which you might have missed

https://www.mrexcel.com/board/thread...oster.1132301/

[daniel.mrc]

Thankyou for your guidance, any help on this matter please? TIA

[JeteMc]

As to "a total of individuals that are working that day in that particular shop" that may be accomplished using: =COUNTIFS(C$4:C$45,$B48)
B48:B56 on sheet 2 are populated with the 9 shops.
Row 57 shows an overall total
As to "and then to be displayed something similar to Sheet 3" may be accomplished using:

Please Login or Register  to view this content.

Note that previously blank cells in column B are filled with the shops and the font color is set to white.
As to setting up the schedule given the location conditions, it may be that someone can do that, however I feel that it would be more efficient to manually set up a rotation (one time through) and let Excel continue the rotation into the future.
I did add data validation drop downs to cells D4:P45 which may make it easier to enter the rotation as opposed to typing.
Let us know if you have any questions.