Sorting Arrival and departure times

This may need VBA

Are you OK with that?

I can write basic macros, but this is way beyond my level unfortunately.

Originally Posted by sa1ntj0hn

I can write basic macros, but this is way beyond my level unfortunately.

Hi,

I wasn't asking if you could write the code, I was asking if you'd be comfortable with a solution that used it?

Yes definitely, sorry misunderstood you.

I'm trying to understand what determines the bus number.

I'd originally thought that the chain of timings to decide the order for one bus would be where the system would take the arrival time at a destination and look for a later starting time at that destination, and repeat that loop until the end of the data rows.
Where a later starting time was found for that destination that would be recorded as the next item in the output list.

Once that first bus had completed the day the system would then start the process for a 2nd bus and repeat the process looping through the data which had not been satisfied by bus 1.

However looking at Row 35, the 16:14 from R to O arrives at O at 17:36 - and on your output that 11th journey is the last for bus one.

However there is a later journey starting from O at 21:00 going to R arriving at 21:58 but that is not shown as the 12th journey for bus 1 but the the 25th overall journey, i.e. journey 14 for bus 2.

Can you explain that anomaly in the rules I've assumed?

Hi Richard,

The rules you have assumed are correct. I have been informed that if there is a gap of over 40 minutes before the next departure time then the bus is assumed to have "gone out of service" meaning it has gone off to carry out work elsewhere on the system.

Row 35, the 16:14 from R to O arrives at O at 17:36, later journey starting from O at 21:00 21:00 - 17:36 = 3hr 24 mins which is greater than 40 mins therefore bus has "gone out of service"

I hope that makes sense and sorry for the confusion.

Phil

PS I spotted there was an error in the previous attachment, This one is corrected.

See how you get on with this.

The starting Data first needs to be sorted in ascending order of column a - the Departure Time. I noticed your last item in A39 was not in order. I therefore took a copy and sorted your original sheet.

Enter the maximum number for out of service minutes in F1. Then click the Create Schedule button.
The Clear schedule will clear the schedule

The main macro is below

Please Login or Register  to view this content.

Richard,

This is brilliant, thank you very much indeed. Really appreciate your help and patience.

Phil

Thank you Bo_Ry

My pleasure and thanks for the rep.

Personally I think Bo_ry's solution is exquisitely neat and straightforward.

An observational lesson I've learned from this.

Like Bo_Ry my initial thoughts were that straighforward Excel functions could achieve this.
So I started analysing the problem and decided any function would need to examine the original data and work out the final position of each row.

The intention was to identify where the arrival point was the same as the following row's departure point (and subject to the arrival / departure times being OK) mark each row with the relevant incremental journey number. When all rows were marked a final formula would use a ranking or small formula to list the journeys in the correct order.

I soon identfied that this approach was going to involve quite complicated formulae and decided a 'brute force' macro to loop through the data doing what a human being would naturally do if they were manually doing the task.

In short I didn't spend enough time on the analysis - and I'm always encouraging people here only to resort to macros when standard excel functions can't achieve the task in an efficient way. So a self slap on the wrist.

Bo_Ry succesfully identified that the answer revolved around the Count of the number of each starting points. That simple understanding was the key to this, and once that was understood the rest of the formulae became relatively straightforward.

So the lesson is to think of as many different approaches as possible before evaluating each one for any complexity it might involve, and think think them all through before turning to VBA.