Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 ? |
#3
|
|||
|
|||
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 ? |
#4
|
|||
|
|||
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 ? |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assign Macro | Excel Discussion (Misc queries) | |||
Assign values to names in a drop-down list? | Excel Discussion (Misc queries) | |||
Spinner properties: Minimum value. How do I assign negative value. | Excel Worksheet Functions | |||
Assign to macro missing | Excel Discussion (Misc queries) | |||
Assign Macro to button in Excel doesnt work Any ideas? | Excel Discussion (Misc queries) |