assignment problem

[L. Chung]

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

 

[Don Cameron]

Lilian
This appears from the info given, to be a courier/taxi type operation and as
such would normally be under the control of a 'dispatcher' who would
actually remember ( or be able to ask) where each vehicle and driver is at
any given time.
However as an aid to confirming status try the following set-up of the
worksheet.
Given that Row 1 contains column descriptions
Enter the formula "=a2" (without quotes) in Cell E2
Copy it down as far as necessary. (If you don't like the zeros then use a
conditional "=if(a2="","",a2).
With the cursor anywhere in the table use Data,Filter, Autofilter.
Now when a question arises about a particular vehicle click on the pull-down
in colE and select the vehicle reference. You will now only see the entries
relating to that vehicle.
The reason for the formula - You may not know the vehicle number but you do
know the type and the filter will allow selection of entries "Custom" where
you can specify entries beginning with "x" ( whatever is appropriate).
This will allow initial entry in Col A to provide a selection from vehicles
not on a job.
The Autofilter will also let you examine records by driver, origin, or
destination.

Hope this helps - I have had to make some assumptions as you did not specify
a record sequence of entry or the relative importance of each (eg I imagine
the Start Time is when the job arises rather than pre-booking)

Don