+ Reply to Thread
Results 1 to 16 of 16

Sorting Arrival and departure times

  1. #1
    Registered User
    Join Date
    01-20-2009
    Location
    newcastle
    MS-Off Ver
    Excel 365
    Posts
    56

    Sorting Arrival and departure times

    Hello all,

    I am at a total loss with this one.

    I have 4 x columns of data relating to bus start and end locations and times.
    I am trying to schedule the buses in the most economical way.

    1st bus of the day leaves Departure Point L at 04:45 this will be designated bus number 1. It arrives at point M at 05:00.

    By examining columns A and B, how do I determine the next possible departure time of this bus from point M. and designate it as bus number 1. When it arrives at its destination I need to see what time is most suitable for it to leave.

    In the example, I have supplied the original data and the required output.
    Any help will be gratefully received.

    Thanks
    Pip
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by sa1ntj0hn; 05-20-2019 at 06:16 AM.

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Sorting Arrival and departure times

    Misread the post
    Last edited by samba_ravi; 05-17-2019 at 07:36 AM. Reason: deleted
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    01-20-2009
    Location
    newcastle
    MS-Off Ver
    Excel 365
    Posts
    56

    Re: Sorting Arrival and departure times

    ahh ok thank you
    Last edited by sa1ntj0hn; 05-17-2019 at 07:37 AM.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Sorting Arrival and departure times

    This may need VBA

    Are you OK with that?
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    01-20-2009
    Location
    newcastle
    MS-Off Ver
    Excel 365
    Posts
    56

    Re: Sorting Arrival and departure times

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

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Sorting Arrival and departure times

    Quote Originally Posted by sa1ntj0hn View Post
    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?

  7. #7
    Registered User
    Join Date
    01-20-2009
    Location
    newcastle
    MS-Off Ver
    Excel 365
    Posts
    56

    Re: Sorting Arrival and departure times

    Yes definitely, sorry misunderstood you.

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Sorting Arrival and departure times

    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?

  9. #9
    Registered User
    Join Date
    01-20-2009
    Location
    newcastle
    MS-Off Ver
    Excel 365
    Posts
    56

    Re: Sorting Arrival and departure times

    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.
    Attached Files Attached Files

  10. #10
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Sorting Arrival and departure times

    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.
    Attached Files Attached Files

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Sorting Arrival and departure times

    Please try at
    G3
    =COUNTIF(I$3:I3,"L")

    H3
    =IFERROR(AGGREGATE(15,6,$A$3:$A$39/($B$3:$B$39=J2)/($A$3:$A$39>=K2)/($A$3:$A$39-"0:50"<=K2),1),AGGREGATE(15,6,$A$3:$A$39/($B$3:$B$39="L"),COUNTIF(I$2:I2,"L")+1))

    I3:K3
    =VLOOKUP($H3,$A$3:$D$39,MATCH(I$2,$A$2:$D$2,),)
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    01-20-2009
    Location
    newcastle
    MS-Off Ver
    Excel 365
    Posts
    56

    Re: Sorting Arrival and departure times

    Richard,

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

    Phil

  13. #13
    Registered User
    Join Date
    01-20-2009
    Location
    newcastle
    MS-Off Ver
    Excel 365
    Posts
    56

    Re: Sorting Arrival and departure times

    Thank you Bo_Ry

  14. #14
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Sorting Arrival and departure times

    My pleasure and thanks for the rep.

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

  15. #15
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Sorting Arrival and departure times

    Richard, thanks for the rep and very kind comment.

  16. #16
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Sorting Arrival and departure times

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Start new animation with same departure point as arrival point in previous slide
    By isabelle.r in forum PowerPoint Formatting & General
    Replies: 1
    Last Post: 03-21-2019, 04:01 AM
  2. Replies: 26
    Last Post: 05-18-2018, 06:15 AM
  3. calculating inter arrival times and statistical distribution of these times
    By ditmar.rijk in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-19-2014, 03:18 AM
  4. Replies: 2
    Last Post: 05-30-2013, 12:45 PM
  5. [SOLVED] Formula to count number of nights falling in each month from arrival and departure dates
    By zicitron in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-28-2013, 02:16 AM
  6. Replies: 0
    Last Post: 04-10-2013, 09:39 AM
  7. [SOLVED] Calculating the number of people in building based on arrival and departure times
    By dvs in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-01-2012, 09:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1