Hi all,

I have a job assignment problem that assigning right car to the right job
without time overlapping. But there is one more requirement. Once the car is
assigned to Driver A, other drivers cannot use the car until Driver A drives
the car back to the depot.

Below table shows the data required:
Column A stores the service type. There are 3 types which are "T", "L" and
"W" services.
Column B and C store the start time and finish time of the job.
Column D and E are the driver and car assignment for the job. e.g. in row 2,
Driver 3 is assigned for the job. The values in column D are given and I
would like to find out the value of column E.
Column F and column G are the routes of the cars (from which location to
which location). The values of these two columns are also given. And location
16 is the depot. So, if the car start at location 16, that means the car go
to the destination from depot.

A B C D E F G
R1 service start end driver car from to
R2 L 12:15 12:40 3 L1 16 32
R3 W 12:20 12:40 2 W1 16 17
R4 T 12:20 12:35 17 T1 16 28
R5 W 12:25 12:45 1 W2 16 61
R6 L 12:25 12:40 18 L2 16 70
R7 W 12:30 12:50 5 W3 16 66
R8 T 12:35 12:50 17 T1 28 17
R9 L 12:35 12:50 4 L3 16 62
R10 W 12:40 13:00 2 W1 17 41
R11 T 12:40 12:55 19 T2 16 61
R12 L 12:40 12:55 3 L1 32 46

You can see that Driver3 (in row 2 and row 12) finished the first job and go
to the location of last job without returning depot. So, he should use the
same car L1. Also, car L1 cannot be used by other drivers in this period.

I show the required results in column E. Actually, one spreadsheet has more
than 150 jobs and assuming that all the jobs are in the same day. There are 3
vehicles (W1, W2 and W3) for €œW€� services, 6 vehicles (L1, L2, L3, L4, L5 and
L6) for €œL€� services and 10 vehicles (T1, T2,€¦,T9 and T10) for €œT€� services.

So, can anyone help me to solve this problem as it is quite urgent?

Thanks very much in advance and appreciate for the help.

Best regards,
Lilian