Staggered Start Times Based on Run Routes

Brutality

New Member
Joined
Feb 5, 2003
Messages
44
Hello again,

I had some good help yesterday assigning Parking IDs for car's in a rally based on run routes (short, medium, and long). See here for the thread:
http://www.mrexcel.com/forum/showthread.php?p=2661964&posted=1#post2661964

I've also made plenty of progress in other required areas and have my macros for separating and extracting the different run routes for mailing envelope labels, but have hit a snag with assigning start times because they have to be staggered but alternating based on the following *rules*:

- The first car starts at 9:15am
- Cars proceed thereafter at 30 second intervals
- Order of departure is based on run route (be aware there are FAR more entries in the medium route) and the logic is:
-- medium, short, medium, short until all the short runs have departed
-- then it continues medium, long, medium, long until all the long runs have departed
-- then all remaining mediums.

Obviously there are far more entries in the medium run than any other, hence the above staggering process and I have a count of how many are in each run based on the above Park IDs if that helps.

Short Run: 223 (IDs 1000-1222)
Medium Run: 1780 (IDs 2000-3779)
Long Run: 355 (4000-4354)

So for example,

Park ID, Start Time
2000, 9:15:00 <--medium
1000, 9:15:30 <--short
2001, 9:16:00 <--medium
1001, 9:16:30 <--short
[...]
2223, 10:05:00 <--medium (guessing the time for demo purposes)
4001, 10:05:30 <--long
2223, 10:06:00 <--medium
4001, 10:06:30 <--long
[...]
4224, 11:15:00 <--remaining mediums
4225, 11:15:30
4226, 11:16:00

I'm really not even sure where to begin on this but am hoping it's possible so I don't have to do it manually. Any ideas?

All help appreciated.

TIA
Mark
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,545
I made a little table with defined names for the rows and columns, like Short, Medium, Long, and Start, and Number, and used those in a formula to calculate the output ...
Excel Workbook
ABCDEFG
1Park IDStart TimeStartNumber
2200009:15:00Short999223
3100009:15:30Medium19991780
4200109:16:00Long3999355
5100109:16:30
Sheet3
 

Brutality

New Member
Joined
Feb 5, 2003
Messages
44
Glenn,

Thanks so much, that works a treat! Using VLOOKUP I am able to copy the times back to the master sheet and all is working well. One small glitch in my logic however - the order is actually now going to be medium long medium long ... then medium short medium short ... then all remaining mediums. I'm looking at your formula now and it's not just a simple matter of replacing any short occurrence with a long one (as the maths breaks when I do that). I will persevere and am pretty sure I can work this out, but if you can easily modify your formula without the head-banging I am now experiencing that would also be great.

BTW, your signature gave me a good laugh.

Thanks again,
Mark
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,545
Glad you like the signature ( and the formula!!! ).

Here's the same idea but for medium-long-medium ... medium-short-medium ... medium-medium:
Code:
=IF((ROW()-1)>(Short Number+Medium Number+Long Number),"",IF(MOD(ROW(),2)=0,COUNTIF(A1:$A$1,">="&Medium Start)-COUNTIF(A1:$A$1,">="&Long Start)+Medium Start+1,IF(COUNTIF(A1:$A$1,">"&Long Start)>=Long Number,IF(COUNTIF(A1:$A$1,"<"&Medium Start)>=Short Number,COUNTIF(A1:$A$1,">="&Medium Start)-COUNTIF(A1:$A$1,">="&Long Start)+Medium Start+1,COUNTIF(A1:$A$1,"<"&Medium Start)+Short Start+1),COUNTIF(A1:$A$1,">"&Long Start)+Long Start+1)))
 

Brutality

New Member
Joined
Feb 5, 2003
Messages
44
Ahh now I see. I had to break it down and format the code with line breaks and indents to understand the logic based on the named range information (I'm a Coldfusion and SilverStripe developer by day but don't spend a lot of time in spreadsheets or using VB but this was an interesting exercise).

Once again, you've saved me a considerable amount of time and it is appreciated.

Mark

P.S I'm from NZ so the only difference is we like our beer cold!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top