Hi all,
This one seems like a biggy. I've got a few ideas of how to run parts of what I'm trying to do here, but before I get into that I'll show my before and after sheets.
Before:
After:
Reference Data:
So what I'm trying to do is get a script to take data from the activity table and place it on the rota where "available".
Requirements are that an activity cannot be split up - for example, on the table employees 1, 2 & 5 are "available" at 9, but only 1 & 5 have the full 4 15 minute slots (the one hour of activity 1) so only employees 1 & 5 can be given activity 1.
I would like the code to have some sort of randbetween function where any employee with the full number of slots available at the correct times for an activity is picked from at random, rather than it being given to simply the first employee available - so in the example Activity 1 could have been given to Employee 1 or 5, Activity 2 could have gone to any combination of 1,3,4 and 5 .
Any activity where no employee is available should be dumped on the right hand side (first column with no employee - so H in the example with activity 3).
I should also note that my actual sheet has more that 4 employees but varies (so xlRight), times on the actual sheet go from A4 to A148, and the activities table is just an example, the real one will also have a variable amount of activities that last for various amounts of time (but always in 15 minute intervals). The last column of this table denotes how many staff members are required for that given activity (never more than this number, but if not all can be met, dump the excess to the right as before) - very unlikely to go over 2 but some flexibility there wouldn't hurt.
I appreciate this is a big ask (or I think it is as I only know how to do individual bit of the requirements - not all together) so any help or input would be a big help here.
Thanks all!
All formatting (colours, fonts etc) are already handled.
This one seems like a biggy. I've got a few ideas of how to run parts of what I'm trying to do here, but before I get into that I'll show my before and after sheets.
Before:
| Rota 3.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Employee | Employee 1 | Employee 2 | Employee 3 | Employee 4 | Employee 5 | |||
| 2 | Blank Row | ||||||||
| 3 | Time | 09:00 - 17:00 | 09:00 - 17:00 | 09:00 - 17:00 | 09:00 - 17:00 | 09:00 - 17:00 | |||
| 4 | 09:00 | 09:15 | Available | Available | Unavailable | Unavailable | Available | ||
| 5 | 09:15 | 09:30 | Available | Available | Available | Unavailable | Available | ||
| 6 | 09:30 | 09:45 | Available | Unavailable | Available | Unavailable | Available | ||
| 7 | 09:45 | 10:00 | Available | Unavailable | Available | Unavailable | Available | ||
| 8 | 10:00 | 10:15 | Available | Unavailable | Available | Unavailable | Available | ||
| 9 | 10:15 | 10:30 | Available | Unavailable | Available | Unavailable | Available | ||
| 10 | 10:30 | 10:45 | Available | Unavailable | Available | Available | Available | ||
| 11 | 10:45 | 11:00 | Available | Unavailable | Available | Available | Available | ||
| 12 | 11:00 | 11:15 | Unavailable | Unavailable | Available | Available | Available | ||
| 13 | 11:15 | 11:30 | Unavailable | Unavailable | Available | Available | Available | ||
| 14 | 11:30 | 11:45 | Available | Unavailable | Available | Available | Available | ||
| 15 | 11:45 | 12:00 | Available | Unavailable | Available | Available | Available | ||
| 16 | 12:00 | 12:15 | Available | Unavailable | Available | Available | Available | ||
| 17 | 12:15 | 12:30 | Available | Unavailable | Available | Available | Available | ||
| 18 | 12:30 | 12:45 | Available | Unavailable | Available | Available | Available | ||
| 19 | 12:45 | 13:00 | Available | Unavailable | Available | Available | Available | ||
| 20 | 13:00 | 13:15 | Available | Unavailable | Unavailable | Available | Available | ||
| 21 | 13:15 | 13:30 | Available | Unavailable | Unavailable | Available | Unavailable | ||
| 22 | 13:30 | 13:45 | Available | Available | Unavailable | Available | Unavailable | ||
| 23 | 13:45 | 14:00 | Available | Available | Available | Available | Unavailable | ||
| 24 | 14:00 | 14:15 | Unavailable | Available | Available | Available | Available | ||
| 25 | 14:15 | 14:30 | Unavailable | Available | Unavailable | Available | Available | ||
| 26 | 14:30 | 14:45 | Unavailable | Available | Unavailable | Available | Available | ||
| 27 | 14:45 | 15:00 | Unavailable | Available | Unavailable | Available | Available | ||
| 28 | 15:00 | 15:15 | Unavailable | Available | Available | Available | Available | ||
| 29 | 15:15 | 15:30 | Unavailable | Available | Available | Unavailable | Available | ||
| 30 | 15:30 | 15:45 | Unavailable | Unavailable | Unavailable | Unavailable | Available | ||
| 31 | 15:45 | 16:00 | Unavailable | Available | Unavailable | Available | Unavailable | ||
| 32 | 16:00 | 16:15 | Unavailable | Available | Unavailable | Available | Unavailable | ||
| 33 | 16:15 | 16:30 | Unavailable | Available | Available | Available | Available | ||
| 34 | 16:30 | 16:45 | Unavailable | Available | Available | Available | Available | ||
| 35 | 16:45 | 17:00 | Unavailable | Available | Available | Available | Available | ||
Sheet1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B4:B35 | B4 | =A4+TIME(0,15,0) |
| A5:A35 | A5 | =B4 |
After:
| Rota 3.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | Employee | Employee 1 | Employee 2 | Employee 3 | Employee 4 | Employee 5 | ||||
| 2 | Blank Row | |||||||||
| 3 | Time | 09:00 - 17:00 | 09:00 - 17:00 | 09:00 - 17:00 | 09:00 - 17:00 | 09:00 - 17:00 | ||||
| 4 | 09:00 | 09:15 | Activity 1 | Available | Unavailable | Unavailable | Available | |||
| 5 | 09:15 | 09:30 | Activity 1 | Available | Available | Unavailable | Available | |||
| 6 | 09:30 | 09:45 | Activity 1 | Unavailable | Available | Unavailable | Available | |||
| 7 | 09:45 | 10:00 | Activity 1 | Unavailable | Available | Unavailable | Available | |||
| 8 | 10:00 | 10:15 | Available | Unavailable | Available | Unavailable | Available | |||
| 9 | 10:15 | 10:30 | Available | Unavailable | Available | Unavailable | Available | |||
| 10 | 10:30 | 10:45 | Available | Unavailable | Available | Available | Available | |||
| 11 | 10:45 | 11:00 | Available | Unavailable | Available | Available | Available | |||
| 12 | 11:00 | 11:15 | Unavailable | Unavailable | Available | Available | Available | |||
| 13 | 11:15 | 11:30 | Unavailable | Unavailable | Available | Available | Available | |||
| 14 | 11:30 | 11:45 | Available | Unavailable | Available | Available | Available | |||
| 15 | 11:45 | 12:00 | Available | Unavailable | Available | Available | Available | |||
| 16 | 12:00 | 12:15 | Available | Unavailable | Available | Activity 2 | Activity 2 | |||
| 17 | 12:15 | 12:30 | Available | Unavailable | Available | Activity 2 | Activity 2 | |||
| 18 | 12:30 | 12:45 | Available | Unavailable | Available | Activity 2 | Activity 2 | |||
| 19 | 12:45 | 13:00 | Available | Unavailable | Available | Activity 2 | Activity 2 | |||
| 20 | 13:00 | 13:15 | Available | Unavailable | Unavailable | Available | Available | |||
| 21 | 13:15 | 13:30 | Available | Unavailable | Unavailable | Available | Unavailable | |||
| 22 | 13:30 | 13:45 | Available | Available | Unavailable | Available | Unavailable | |||
| 23 | 13:45 | 14:00 | Available | Available | Available | Available | Unavailable | |||
| 24 | 14:00 | 14:15 | Unavailable | Available | Available | Available | Available | |||
| 25 | 14:15 | 14:30 | Unavailable | Available | Unavailable | Available | Available | |||
| 26 | 14:30 | 14:45 | Unavailable | Available | Unavailable | Available | Available | |||
| 27 | 14:45 | 15:00 | Unavailable | Available | Unavailable | Available | Available | |||
| 28 | 15:00 | 15:15 | Unavailable | Available | Available | Available | Available | |||
| 29 | 15:15 | 15:30 | Unavailable | Available | Available | Unavailable | Available | |||
| 30 | 15:30 | 15:45 | Unavailable | Unavailable | Unavailable | Unavailable | Available | Activity 3 | ||
| 31 | 15:45 | 16:00 | Unavailable | Available | Unavailable | Available | Unavailable | Activity 3 | ||
| 32 | 16:00 | 16:15 | Unavailable | Available | Unavailable | Available | Unavailable | |||
| 33 | 16:15 | 16:30 | Unavailable | Available | Available | Available | Available | |||
| 34 | 16:30 | 16:45 | Unavailable | Available | Available | Available | Available | |||
| 35 | 16:45 | 17:00 | Unavailable | Available | Available | Available | Available | |||
Sheet2 | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B4:B35 | B4 | =A4+TIME(0,15,0) |
| A5:A35 | A5 | =B4 |
Reference Data:
| Activity | Start time | Finish time | Staff required |
| Activity 1 | 09:00 | 10:00 | 1 |
| Activity 2 | 12:00 | 13:00 | 2 |
| Activity 3 | 15:30 | 16:00 | 1 |
So what I'm trying to do is get a script to take data from the activity table and place it on the rota where "available".
Requirements are that an activity cannot be split up - for example, on the table employees 1, 2 & 5 are "available" at 9, but only 1 & 5 have the full 4 15 minute slots (the one hour of activity 1) so only employees 1 & 5 can be given activity 1.
I would like the code to have some sort of randbetween function where any employee with the full number of slots available at the correct times for an activity is picked from at random, rather than it being given to simply the first employee available - so in the example Activity 1 could have been given to Employee 1 or 5, Activity 2 could have gone to any combination of 1,3,4 and 5 .
Any activity where no employee is available should be dumped on the right hand side (first column with no employee - so H in the example with activity 3).
I should also note that my actual sheet has more that 4 employees but varies (so xlRight), times on the actual sheet go from A4 to A148, and the activities table is just an example, the real one will also have a variable amount of activities that last for various amounts of time (but always in 15 minute intervals). The last column of this table denotes how many staff members are required for that given activity (never more than this number, but if not all can be met, dump the excess to the right as before) - very unlikely to go over 2 but some flexibility there wouldn't hurt.
I appreciate this is a big ask (or I think it is as I only know how to do individual bit of the requirements - not all together) so any help or input would be a big help here.
Thanks all!
All formatting (colours, fonts etc) are already handled.
