Car assign simulation
 

Hi all,

I have a question as follows:
A B C D E
----------------------------------------------------------
Services Required Time Staff Car
Start Finish Provide
----------------------------------------------------------
1 T 1:20 1:35 12 ?
2 W 8:00 8:20 1 ?
3 W 8:10 8:30 2 ?
4 T 8:15 8:30 14 ?
5 W 8:20 8:40 3 ?
6 T 8:25 8:40 15 ?
7 T 8:35 8:50 16 ?

IF there are(sheet2):
CarW1 for W Services
CarW2 for W Services
CarT1 for T Services
CarT2 for T Services

How can I use a formula to assign the right car to the Coloum "Car Provide",
with no overlapping in time?

Thanks in advance.

Best Rgds,
L. Chung

Hi
My solution requires 4 additional colums.
Maybe you'll see a better solution later.
First, I'm renumbering your your row assignments to account for the
header rows. This solution requires at least one header row.


A B C D E
1 ----------------------------------------------------------
2 Services Required Time Staff Car
3 Start Finish Provide
4 ----------------------------------------------------------
5 T 1:20 1:35 12 ?
6 W 8:00 8:20 1 ?
7 W 8:10 8:30 2 ?
8 T 8:15 8:30 14 ?
9 W 8:20 8:40 3 ?
10 T 8:25 8:40 15 ?
11 T 8:35 8:50 16 ?

Add columns F to I with the following:

F G H I
1
2
3 W1 W2 T1 T2
4 -------------------------------------------

In F5: =IF(A5="T",0,C5)
In H5: =IF(A5="W","",C5)
In F6: =IF(A6="T",0,IF(MAX(F$5:F5)B6,0,C6))
In G6: =IF(OR(A6="T",F60),0,IF(MAX(G$5:G5)B6,0,C6))
In H6: =IF(A6="W",0,IF(MAX(H$5:H5)B6,0,C6))
In I6: =IF(OR(A6="W",H60),0,IF(MAX(I$5:I5)B51,C6))

Copy F6:I6 down

In E5:
=IF(SUM(F5:I5)=0,"N/A",INDEX(F$3:I$3,1,MATCH(MAX(F5:I5),F5:I5,0)))
Copy E5 down

Hope this helps, or least gets you on the right track.
Bob




On Fri, 8 Apr 2005 07:07:04 -0700, "L. Chung"
wrote:

A B C D E
----------------------------------------------------------
Services Required Time Staff Car
Start Finish Provide
----------------------------------------------------------
1 T 1:20 1:35 12 ?
2 W 8:00 8:20 1 ?
3 W 8:10 8:30 2 ?
4 T 8:15 8:30 14 ?
5 W 8:20 8:40 3 ?
6 T 8:25 8:40 15 ?
7 T 8:35 8:50 16 ?

Hi Bob and all,

I have tried the formula and it is workable. Howeve, if the number of
services is increased (e.g. 25 "T" services and 10 "W" services) and the
number of cars increased (e.g. there are W1, W2, W3, T1, T2, T3, T4, T5), how
can I modify the formulae? Please kindly advise.

Thank in advance.

"Bob Tarburton" wrote:


Hi
My solution requires 4 additional colums.
Maybe you'll see a better solution later.
First, I'm renumbering your your row assignments to account for the
header rows. This solution requires at least one header row.


A B C D E
1 ----------------------------------------------------------
2 Services Required Time Staff Car
3 Start Finish Provide
4 ----------------------------------------------------------
5 T 1:20 1:35 12 ?
6 W 8:00 8:20 1 ?
7 W 8:10 8:30 2 ?
8 T 8:15 8:30 14 ?
9 W 8:20 8:40 3 ?
10 T 8:25 8:40 15 ?
11 T 8:35 8:50 16 ?

Add columns F to I with the following:

F G H I
1
2
3 W1 W2 T1 T2
4 -------------------------------------------

In F5: =IF(A5="T",0,C5)
In H5: =IF(A5="W","",C5)
In F6: =IF(A6="T",0,IF(MAX(F$5:F5)B6,0,C6))
In G6: =IF(OR(A6="T",F60),0,IF(MAX(G$5:G5)B6,0,C6))
In H6: =IF(A6="W",0,IF(MAX(H$5:H5)B6,0,C6))
In I6: =IF(OR(A6="W",H60),0,IF(MAX(I$5:I5)B51,C6))

Copy F6:I6 down

In E5:
=IF(SUM(F5:I5)=0,"N/A",INDEX(F$3:I$3,1,MATCH(MAX(F5:I5),F5:I5,0)))
Copy E5 down

Hope this helps, or least gets you on the right track.
Bob




On Fri, 8 Apr 2005 07:07:04 -0700, "L. Chung"
wrote:

A B C D E
----------------------------------------------------------
Services Required Time Staff Car
Start Finish Provide
----------------------------------------------------------
1 T 1:20 1:35 12 ?
2 W 8:00 8:20 1 ?
3 W 8:10 8:30 2 ?
4 T 8:15 8:30 14 ?
5 W 8:20 8:40 3 ?
6 T 8:25 8:40 15 ?
7 T 8:35 8:50 16 ?

Yes, it can be done, and i'll get back to you monday morning latest.

Where this really falls down is the next day or when you have 10 to 11
AM entries and then go to early PM entries.
The easiest fix for that is date and time formatted as time only, or
use military time and start a new sheet each day (won't work if you're
open through the midnight hour). But I can give you a solution for
that also.

Later
Bob

On Fri, 8 Apr 2005 12:49:03 -0700, "L. Chung"
wrote:

Hi Bob and all,

I have tried the formula and it is workable. Howeve, if the number of
services is increased (e.g. 25 "T" services and 10 "W" services) and the
number of cars increased (e.g. there are W1, W2, W3, T1, T2, T3, T4, T5), how
can I modify the formulae? Please kindly advise.

Thank in advance.

"Bob Tarburton" wrote:


Hi
My solution requires 4 additional colums.
Maybe you'll see a better solution later.
First, I'm renumbering your your row assignments to account for the
header rows. This solution requires at least one header row.


A B C D E
1 ----------------------------------------------------------
2 Services Required Time Staff Car
3 Start Finish Provide
4 ----------------------------------------------------------
5 T 1:20 1:35 12 ?
6 W 8:00 8:20 1 ?
7 W 8:10 8:30 2 ?
8 T 8:15 8:30 14 ?
9 W 8:20 8:40 3 ?
10 T 8:25 8:40 15 ?
11 T 8:35 8:50 16 ?

Add columns F to I with the following:

F G H I
1
2
3 W1 W2 T1 T2
4 -------------------------------------------

In F5: =IF(A5="T",0,C5)
In H5: =IF(A5="W","",C5)
In F6: =IF(A6="T",0,IF(MAX(F$5:F5)B6,0,C6))
In G6: =IF(OR(A6="T",F60),0,IF(MAX(G$5:G5)B6,0,C6))
In H6: =IF(A6="W",0,IF(MAX(H$5:H5)B6,0,C6))
In I6: =IF(OR(A6="W",H60),0,IF(MAX(I$5:I5)B51,C6))

Copy F6:I6 down

In E5:
=IF(SUM(F5:I5)=0,"N/A",INDEX(F$3:I$3,1,MATCH(MAX(F5:I5),F5:I5,0)))
Copy E5 down

Hope this helps, or least gets you on the right track.
Bob




On Fri, 8 Apr 2005 07:07:04 -0700, "L. Chung"
wrote:

A B C D E
----------------------------------------------------------
Services Required Time Staff Car
Start Finish Provide
----------------------------------------------------------
1 T 1:20 1:35 12 ?
2 W 8:00 8:20 1 ?
3 W 8:10 8:30 2 ?
4 T 8:15 8:30 14 ?
5 W 8:20 8:40 3 ?
6 T 8:25 8:40 15 ?
7 T 8:35 8:50 16 ?

Very little change from my previous

In G6: =IF(OR(A6="T",SUM($F6:F6)0),0,IF(MAX(G$5:G5)B6,0 ,C6))
In I6: =IF(OR(A6="W",SUM($H6:H6)0),0,IF(MAX(I$5:I5)B51, C6))

Now insert columns before H for as many "W" cars as you have,
add "T" cars to the right, and add your car labels in row 3

Copy the G6 formula across and down for W cars and the I6 formula
across and down for T cars (cars limited by the 256 column less a few
columns used for start, stop, etc)

Now adjust the formula in E5,
=IF(SUM(F5:I5)=0,"N/A",INDEX(F$3:I$3,1,MATCH(MAX(F5:I5),F5:I5,0)))
so that the F5:I5 and F$3:I$3 ranges extend across all your
columns/cars.


To deal with the "" signs in the formulae, you have to type in your
start and stop times as date time AM/PM, such as "4/8/2005 2:15 PM"
Or you could use a four columns each for start and stop time
For example (not to previous scale):
A B C D
1 Start Start
2 Date Hour Minutes Time
3 4/8 14 15

D3 is =A3+B3/24+C3/(24*60)
(or 1440 for 24*60)

and point your start times (column B in the previous formulae) to the
result in column D (same idea with stop times)

You have to use "military" hours, 0 to 23, and this method also
"wastes" 6 columns
The advantage is you can copy down the date so this requires a little
less typing per booking in the end (about the same as you had in the
original example)

Of course you'll probably want to cut the D column out to the right as
it is not part of your input output/output array.

I hope this gets you there.
I probabaly won't check back 'til Monday, and others might not look at
this thread because it's been "answered".
If you need more, start a new thread, or I'll catch you next week.

Bob

Hi Bob,

Thanks for your quick response. I have another more complicated problem but
it is also a car assignment problem.

The problem is similar to the previous one that assigning right car to the
right job without time overlapping. But there is one more requirement. Once
the car is assigned to DriverA, other drivers cannot use the car until
DriverA drives the car back to the depot.

Using below table,
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 snd finish time of the job.
Column D and E are the driver and car assignment for the job. e.g. in row 4,
Driver 3 is assignned 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 route of the car (from which location to which
location). The value 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.
I have inputted the formulae based on those you taught me before .

A B C D E F G H I
J K L M N O P

----------------------------------------------------------------------------------------------
1) service| job | assignment | location | W truck | L truck
| T truck |
2) type |start| end |driver|vehicle|from| to |W1|W2|W3|L1|L2|L3|T1|T2|T3|

3)---------------------------------------------------------------------------------------------
4) L 12:15 12:40 3 L1 16 32
5) W 12:20 12:40 2 W1 16 17
6) T 12:20 12:35 17 T1 16 28
7) W 12:25 12:45 1 W2 28 61
8) L 12:25 12:40 18 L2 16 70
9) W 12:30 12:50 5 W3 16 66
10) T 12:35 12:50 14 T1 22 17
11) L 12:35 12:50 4 L1 16 62
12) W 12:40 13:00 2 W1 17 41
13) T 12:40 12:55 19 T2 16 61
14) L 12:40 12:55 3 L2 32 46

in H4: =IF(A4="W",C4,0)
in K4: =IF(A4="L",C4,0)
in N4: =IF(A4="T",C4,0)
in H5: =IF(A5="W",IF(MAX(H$4:H4)B5,0,C5),0)
in I5: =IF(OR(SUM($H5:H5)0,$A5="T",$A5="l"),0,IF(MAX(I$4 :I4)$B5,0,$C5))
in J5: =IF(OR(SUM($H5:I5)0,$A5="T",$A5="l"),0,IF(MAX(J$4 :J4)$B5,0,$C5))
in K5: =IF(A5="L",IF(MAX(K$4:K4)$B5,0,$C5),0)
in L5: =IF(OR(SUM($K5:K5)0,$A5="W",$A5="T"),0,IF(MAX(L$4 :L4)$B5,0,$C5))
in M5: =IF(OR(SUM($K5:L5)0,$A5="W",$A5="T"),0,IF(MAX(M$4 :M4)$B5,0,$C5))
in N5: =IF(A5="T",IF(MAX(N$4:N4)B5,0,C5),0)
in O5: =IF(OR(SUM($N5:N5)0,$A5="W",$A5="L"),0,IF(MAX(O$4 :O4)$B5,0,$C5))
in P5: =IF(OR(SUM($N5:O5)0,$A5="W",$A5="L"),0,IF(MAX(P$4 :P4)$B5,0,$C5))

The outputs of column E are shown.

However, you can see that Driver3 (in row 4 and row 14) 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.

So, can you plaese help me to solve this problem? Also, please don't mind my
message is so long and I have so many questions.

Many thanks for your help.

Best regards,
Lilian

It is getting rather complicated!
If you email me your spreadsheet I'll see what I can do.
Just remove the ".remove" part from my email.

On Mon, 11 Apr 2005 09:40:02 -0700, "L. Chung"
wrote:

So, can you plaese help me to solve this problem? Also, please don't mind my
message is so long and I have so many questions.

Many thanks for your help.