Hello everyone,

In my worksheet, I have a list of events that I need to schedule flights for (both to and from the event).  The flights to be scheduled depend on the time of the event, and there is a large list to look through, so I want to try and automate this each month.

On one sheet, I have a list of the events that I want to schedule flights for.  Each event has a route, along with either the “latest arrival time” (for outbound flights), or “earliest departure time” (for return flights).  These times are compared to a full list of scheduled flights (Sheet 2).   I’ve also attached a sample with the expected results.

For the outbound flight:

For the listed route (column B), look at the ARRIVAL times for flights in Sheet 2Return the flight (flt #, departure time, arrival time) for the flight is closest to – but no later than – the “out latest arrival” date/time (column c)

So for event A:  LAXPHX, with latest arrival time of 7/1 @ 20:30.  There is a LAXPHX flight that arrives at 20:39, but since that is after the “latest arrival”, the next earliest one would be flight # 5, with an arrival time of 19:44.  This information is returned to columns D-F on Sheet 1

For the return flight,

For the listed route (column H), look at the DEPARTURE times in Sheet 2Return the flight (flt #, departure date/time, arrival date/time) for the flight that is closest to – but no earlier than – the “return earliest departure” date/time (column I)

The return for event A:  PHXLAX, earliest departure of 7/3 @ 5:45.  There is a PHXLAX flight that departs @ 5:30, but that is too early; the desired one is flt # 14, departing @ 6:25, and arriving @ 7:19.  This information is returned to columns J-L on Sheet 1.

Note:  if it will be simpler to put the scheduled flights for each route in different sheets, that will be fine!

Thank you for any guidance you can provide!

​Hello everyone, In my worksheet, I have a list of events that I need to schedule flights for (both to and from the event).  The flights to be scheduled depend on the time of the event, and there is a large list to look through, so I want to try and automate this each month. On one sheet, I have a list of the events that I want to schedule flights for.  Each event has a route, along with either the “latest arrival time” (for outbound flights), or “earliest departure time” (for return flights).  These times are compared to a full list of scheduled flights (Sheet 2).   I’ve also attached a sample with the expected results. For the outbound flight:For the listed route (column B), look at the ARRIVAL times for flights in Sheet 2Return the flight (flt #, departure time, arrival time) for the flight is closest to – but no later than – the “out latest arrival” date/time (column c) So for event A:  LAXPHX, with latest arrival time of 7/1 @ 20:30.  There is a LAXPHX flight that arrives at 20:39, but since that is after the “latest arrival”, the next earliest one would be flight # 5, with an arrival time of 19:44.  This information is returned to columns D-F on Sheet 1 For the return flight, For the listed route (column H), look at the DEPARTURE times in Sheet 2Return the flight (flt #, departure date/time, arrival date/time) for the flight that is closest to – but no earlier than – the “return earliest departure” date/time (column I) The return for event A:  PHXLAX, earliest departure of 7/3 @ 5:45.  There is a PHXLAX flight that departs @ 5:30, but that is too early; the desired one is flt # 14, departing @ 6:25, and arriving @ 7:19.  This information is returned to columns J-L on Sheet 1. Note:  if it will be simpler to put the scheduled flights for each route in different sheets, that will be fine! Thank you for any guidance you can provide!  Read More

​