Staggered Start Times Based on Run Routes

[Brutality]

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

 

[GlennUK]

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
	A	B	C	D	E	F	G
1	Park ID	Start Time				Start	Number
2	2000	09:15:00			Short	999	223
3	1000	09:15:30			Medium	1999	1780
4	2001	09:16:00			Long	3999	355
5	1001	09:16:30
Sheet3

 

[Brutality]

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]

Glad you like the signature ( and the formula!!! ).

Here's the same idea but for medium-long-medium ... medium-short-medium ... medium-medium:

=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]

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!!!!