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 ?