Autosort workers in working schedule on the basis of two criteria

[trinitron86]

Hello.

I would really appreciate any help on this.

I am trying to create a working schedule in table 3 (tab 3) that autosort available caregivers from table 1 (tab 1) to caretakers from table 2 (tab 2) on a specific day of month. Generated schedule must consider that caregivers work only on specific days of month and can offer their care only to some of caretakers (which allow to work with them). Each caregiver spends 1 hour of his/her working time at one caretaker so that he/she can visit more than one caretaker per day. Daily working hours must be roughly divided among available caregivers.

Table 1 shows which caregiver is available for work on a specific day of month.
Table 2 shows which caretaker allows specific caregiver to offer him a care (work with him).

I've attached the spreadsheet I am working on.

Appreciate any help possible

[soledad]

Hi trinitron86
You try this array formula for cell B3 in Table 3

Please Login or Register  to view this content.

Confirm it with CTRL+SHIFT+ENTER theo fill across AF3 then down
Note: There is the function RAND() in above formula, so you can change the Schedule by press F9 button .
Hope it helpfull

[JeteMc]

This is going to fall more into the 'help' than 'solution' category. It primarily considers days when caregivers work and use conditional formatting to identify conflicts between caregiver and caretaker which are then overcome by making changes in the order of the names on table 1/table 3 (done on a monthly basis). It provides a fairly equal distribution of hours between the caregivers, as there should be no more than 1 hour difference between the caregiver(s) getting the most and least hours assigned on any given day.
The formula that populates table 3 is:

Please Login or Register  to view this content.

Note: Jenny is moved to the top of table 1 which greatly reduces the number of conflicts on table 3.
Note: Denis and Alen | George and Neum were switched on table three which reduced the number of conflicts (highlighted in red*).
*The conditional formatting rule that highlights conflicts (the conflict in this case) is:

Please Login or Register  to view this content.

Let me know if you have any questions.