Car assign simulation

[L. Chung]

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

 

[Bob Tarburton]

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",F6>0),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",H6>0),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

 

[L. Chung]

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]

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