Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Values Based on Constraints
I have a employee job scheduling table that I would like to populate
based on criteria from 2 other tables. The scheduling table has the employee names listed in each row and across the top is the period of the day broken into 4 time periods. The critera tables a 1. A training matrix that lists the employee names in each row and the jobs listed across the top. If an employee is trained, the intersecting cell has a "T". If they are not trained, the cell is blank. 2. An attendance table that lists the employee names in each row and if they are absent, there is an "A" in the next cell. If they are at work, the cell is blank. I'd like to be able to populate the scheduling table based on whether they are trained and present at work. Basically, each day the attendance table would be updated and then the scheduling table would be automatically re-calculated. There would need to be provisions to prevent the same employee from being scheduled on the same job more than once a day on a certain job unless no one else was trained. The format of the tables in am using and the output I would like are as follows: Table 1 - Training Matrix (Employees in Col A and Job Names in row 3) A "Y" means that can work that job. Job 1 Job 2 Job 3 Job 4 Job 5 Phil Y Y Y Y Bill Y Y Y Y Joe Y Y Y Y Dan Y Y Y Y Y Tim Y Y Y Y Table 2 - Attendance - An "A" means they are absent & cannot be scheduled Phil Bill Joe A Dan Tim Table 3 - Schedule 7am-9am 9am-11am 11am-1pm 1pm-3pm Phil Job 1 Job 2 Job 4 Job 5 Bill Job 3 Job 4 Job 5 Job 1 Joe Dan Job 2 Job 3 Job 1 Job 4 Tim Job 5 Job 1 Job 3 Job 2 Any advice on the best way to approach this would be appreciated. Thanks Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Values Based on Constraints
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Values Based on Constraints
On Oct 20, 12:24 pm, "Nigel" wrote:
Use vlookup to get the returned values and then combine the results to flag who, what and when. -- Regards, Nigel wrote in message oups.com... I have a employee job scheduling table that I would like to populate based on criteria from 2 other tables. The scheduling table has the employee names listed in each row and across the top is the period of the day broken into 4 time periods. The critera tables a 1. A training matrix that lists the employee names in each row and the jobs listed across the top. If an employee is trained, the intersecting cell has a "T". If they are not trained, the cell is blank. 2. An attendance table that lists the employee names in each row and if they are absent, there is an "A" in the next cell. If they are at work, the cell is blank. I'd like to be able to populate the scheduling table based on whether they are trained and present at work. Basically, each day the attendance table would be updated and then the scheduling table would be automatically re-calculated. There would need to be provisions to prevent the same employee from being scheduled on the same job more than once a day on a certain job unless no one else was trained. The format of the tables in am using and the output I would like are as follows: Table 1 - Training Matrix (Employees in Col A and Job Names in row 3) A "Y" means that can work that job. Job 1 Job 2 Job 3 Job 4 Job 5 Phil Y Y Y Y Bill Y Y Y Y Joe Y Y Y Y Dan Y Y Y Y Y Tim Y Y Y Y Table 2 - Attendance - An "A" means they are absent & cannot be scheduled Phil Bill Joe A Dan Tim Table 3 - Schedule 7am-9am 9am-11am 11am-1pm 1pm-3pm Phil Job 1 Job 2 Job 4 Job 5 Bill Job 3 Job 4 Job 5 Job 1 Joe Dan Job 2 Job 3 Job 1 Job 4 Tim Job 5 Job 1 Job 3 Job 2 Any advice on the best way to approach this would be appreciated. Thanks Jeff- Hide quoted text - - Show quoted text - I'm fairly new to this type of thing in Excel. Could you show me an example ? Thanks Jeff |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Values Based on Constraints
Scheduling is not a simple task. You must set up a set of criteria and use
some best fit algorithm to determine the schedule. The priority should be that each task gets performed with a lower priority that each person be scheduled to a task only once a day. Your example shows the selection order going to task 1 first and task 5 last. With the training matrix you may end up with only one person trained for task 5 and that person may be assigned to task 1. There will be nobody available to do the task 5 operation. The proper way to write this type of program is to try every possible combination of assignments giving a score to each combination. Then choosing the combination that gets the best score. " wrote: On Oct 20, 12:24 pm, "Nigel" wrote: Use vlookup to get the returned values and then combine the results to flag who, what and when. -- Regards, Nigel wrote in message oups.com... I have a employee job scheduling table that I would like to populate based on criteria from 2 other tables. The scheduling table has the employee names listed in each row and across the top is the period of the day broken into 4 time periods. The critera tables a 1. A training matrix that lists the employee names in each row and the jobs listed across the top. If an employee is trained, the intersecting cell has a "T". If they are not trained, the cell is blank. 2. An attendance table that lists the employee names in each row and if they are absent, there is an "A" in the next cell. If they are at work, the cell is blank. I'd like to be able to populate the scheduling table based on whether they are trained and present at work. Basically, each day the attendance table would be updated and then the scheduling table would be automatically re-calculated. There would need to be provisions to prevent the same employee from being scheduled on the same job more than once a day on a certain job unless no one else was trained. The format of the tables in am using and the output I would like are as follows: Table 1 - Training Matrix (Employees in Col A and Job Names in row 3) A "Y" means that can work that job. Job 1 Job 2 Job 3 Job 4 Job 5 Phil Y Y Y Y Bill Y Y Y Y Joe Y Y Y Y Dan Y Y Y Y Y Tim Y Y Y Y Table 2 - Attendance - An "A" means they are absent & cannot be scheduled Phil Bill Joe A Dan Tim Table 3 - Schedule 7am-9am 9am-11am 11am-1pm 1pm-3pm Phil Job 1 Job 2 Job 4 Job 5 Bill Job 3 Job 4 Job 5 Job 1 Joe Dan Job 2 Job 3 Job 1 Job 4 Tim Job 5 Job 1 Job 3 Job 2 Any advice on the best way to approach this would be appreciated. Thanks Jeff- Hide quoted text - - Show quoted text - I'm fairly new to this type of thing in Excel. Could you show me an example ? Thanks Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text to column and selecting values based on a different column | Excel Worksheet Functions | |||
Text to column and selecting values based on a different column | Excel Discussion (Misc queries) | |||
Selecting range based on values | Excel Programming | |||
Selecting data from a list based on entered values | Excel Discussion (Misc queries) | |||
optimization-selecting a group with constraints | Excel Programming |