sherifhanna82
New Member
- Joined
- Feb 26, 2011
- Messages
- 4
Greetings All,
I have been assigned a task at my work and I am having trouble executing it with Excel. Here's the scenario:
Let's say we have 5 gates at an airport belonging to one airline. The gates are broken into 2 zones, with 1 supervisor for each zone. Zone 1 has gates 1-3 and zone 2 has gates 4-5. The idea is to distribute the flight activity amongst both zones in sequence of their departure and arrival times, while ensuring that there aren't multiple flights in 1 zone while the other has no flight activity. Since all 5 gates will have flights parked at them first thing in the morning (called originators), the departure time of each of those flights will set the sequence/rank of that gate for the next round of flights. Meaning that the gate that had the first flight leave should get the first flight that arrives in the next wave/round of flights and the gate that had the second flight departure should get the second arrival in the next wave/round, etc.
Example:
Here's the line-up for the first five flights in the morning (Round 1):
Gate 1: Flight departs at 6:00
Gate 2: Flight departs at 6:10
Gate 3: Flight departs at 6:10
Gate 4: Flight departs at 6:20
Gate 5: Flight departs at 6:30
Next wave/round of flights (Round 2):
Flight 1: Arrives at 7:00 departs at 7:30
Flight 2: Arrives at 6:55 departs at 7:35
Flight 3: Arrives at 7:10 departs at 7:40
Flight 4: Arrives at 6:50 departs at 7:45
Flight 5: Arrives at 7:20 departs at 8:00
If I use only the departure times for the above example, gate 1 would get flight 1 in the 2nd round and gate 2 would get flight 2. However, that's not the best utilization of gates, because the flights don't have equal ground time and the first departure in any round doesn't always mean that it was the first arrival. So, for the above example, giving gate 1 flight 1 and gate 2 flight 2 would give gate 1, 1 hour between flights while giving gate 2 45 minutes, but if we consider the arrival time of the flights in round two and the departure times in round 1 gate 1 would get flight 2 and gate 2 would get flight 1 giving gate 1 55 minutes and gate 2 50 minutes.
The challenge I am having is getting Excel to become dynamic enough to consider the gate sequence in one round of departures and assign its next flight based on that same sequence, but in the order of arrival of next wave/round of flights. The number of gates is 5 to make things simple, but my task is to build an Excel sheet/tool that can work for any number of gates and/or zones.
The data below is from the excel sheet in which we receive our schedules. The gate assignment of each flight can go in any cell on the corresponding row of its flight number. I understand that this is an elaborate task, but I would greatly appreciate any assistance as I have come to a dead end trying to get this to work.
Thank you and Happy Holidays Season,
S.
<TBODY>
</TBODY><COLGROUP><COL><COL span=7><COL><COL></COLGROUP>
I have been assigned a task at my work and I am having trouble executing it with Excel. Here's the scenario:
Let's say we have 5 gates at an airport belonging to one airline. The gates are broken into 2 zones, with 1 supervisor for each zone. Zone 1 has gates 1-3 and zone 2 has gates 4-5. The idea is to distribute the flight activity amongst both zones in sequence of their departure and arrival times, while ensuring that there aren't multiple flights in 1 zone while the other has no flight activity. Since all 5 gates will have flights parked at them first thing in the morning (called originators), the departure time of each of those flights will set the sequence/rank of that gate for the next round of flights. Meaning that the gate that had the first flight leave should get the first flight that arrives in the next wave/round of flights and the gate that had the second flight departure should get the second arrival in the next wave/round, etc.
Example:
Here's the line-up for the first five flights in the morning (Round 1):
Gate 1: Flight departs at 6:00
Gate 2: Flight departs at 6:10
Gate 3: Flight departs at 6:10
Gate 4: Flight departs at 6:20
Gate 5: Flight departs at 6:30
Next wave/round of flights (Round 2):
Flight 1: Arrives at 7:00 departs at 7:30
Flight 2: Arrives at 6:55 departs at 7:35
Flight 3: Arrives at 7:10 departs at 7:40
Flight 4: Arrives at 6:50 departs at 7:45
Flight 5: Arrives at 7:20 departs at 8:00
If I use only the departure times for the above example, gate 1 would get flight 1 in the 2nd round and gate 2 would get flight 2. However, that's not the best utilization of gates, because the flights don't have equal ground time and the first departure in any round doesn't always mean that it was the first arrival. So, for the above example, giving gate 1 flight 1 and gate 2 flight 2 would give gate 1, 1 hour between flights while giving gate 2 45 minutes, but if we consider the arrival time of the flights in round two and the departure times in round 1 gate 1 would get flight 2 and gate 2 would get flight 1 giving gate 1 55 minutes and gate 2 50 minutes.
The challenge I am having is getting Excel to become dynamic enough to consider the gate sequence in one round of departures and assign its next flight based on that same sequence, but in the order of arrival of next wave/round of flights. The number of gates is 5 to make things simple, but my task is to build an Excel sheet/tool that can work for any number of gates and/or zones.
The data below is from the excel sheet in which we receive our schedules. The gate assignment of each flight can go in any cell on the corresponding row of its flight number. I understand that this is an elaborate task, but I would greatly appreciate any assistance as I have come to a dead end trying to get this to work.
Thank you and Happy Holidays Season,
S.
From | In Flt | Arr Time | Rte | Eqpt | Turn | Dep Time | Out Flt | To | DOW |
335 | 73W | 06:00 | 180 | BWI | Mon | ||||
336 | 73W | 06:10 | 1159 | MDW BUF | Mon | ||||
83 | 733 | 06:10 | 640 | HOU ABQ | Mon | ||||
337 | 73W | 07:05 | 238 | DEN | Mon | ||||
338 | 73W | 07:10 | 697 | PHX SMF BUR LAS SEA | Mon | ||||
1035 | 07:55 | 431 | 73W | 30 | 08:20 | 568 | AUS | Mon | |
1027 | 07:50 | 22 | 733 | 30 | 08:30 | 3566 | SDF BWI | Mon | |
1637 | 08:40 | 186 | 73G | 35 | 09:10 | 299 | ORF BWI LIT HOU ECP | Mon | |
DAL HOU | 1628 | 10:45 | 26 | 733 | 35 | 11:20 | 1628 | BWI MCO MCI PHX ABQ | Mon |
EWR MDW | 1972 | 11:00 | 375 | 73W | 30 | 11:30 | 1972 | LAS DEN HOU MAF | Mon |
PHL MCO | 501 | 11:25 | 288 | 73W | 30 | 11:55 | 501 | SFO SNA PHX AUS | Mon |
PVD BWI ORF | 155 | 11:35 | 446 | 73W | 30 | 12:05 | 155 | LAX SMF | Mon |
BUF BWI | 166 | 11:40 | 196 | 73W | 40 | 12:20 | 166 | MDW DSM | Mon |
DEN MDW | 172 | 12:40 | 220 | 73W | 30 | 13:10 | 172 | ORF BWI BUF LAS SNA | Mon |
AUS | 657 | 13:35 | 248 | 73W | 30 | 14:05 | 657 | SDF MDW OMA | Mon |
BWI MCO SDF | 42 | 14:10 | 25 | 733 | 30 | 14:40 | 42 | HOU DAL LBB | Mon |
SFO | 844 | 14:15 | 476 | 73W | 30 | 14:45 | 844 | MCO MDW IAD | Mon |
PHX | 703 | 15:05 | 317 | 73W | 30 | 15:35 | 703 | MDW PDX SMF | Mon |
DEN | 204 | 15:35 | 376 | 73W | 35 | 16:10 | 204 | BWI ISP MCO | Mon |
SAN PHX LAS | 2280 | 17:05 | 457 | 73W | 35 | 17:40 | 4017 | PHX LAS BUR | Mon |
RDU STL MCI BWI | 3154 | 17:35 | 448 | 73W | 35 | 18:10 | 3154 | DEN | Mon |
ELP SAT HOU JAX ORF | 3662 | 17:50 | 373 | 73W | 30 | 18:20 | 3662 | LAS | Mon |
MDW STL TUL DAL HOU | 29 | 18:00 | 54 | 733 | 30 | 18:30 | 29 | BWI CLE | Mon |
MHT MDW | 1017 | 18:40 | 207 | 73W | 35 | 19:15 | 1017 | AUS DAL | Mon |
BUR PHX | 295 | 19:05 | 261 | 73W | 30 | 19:35 | 295 | MDW DTW | Mon |
LAX | 1346 | 20:05 | 209 | 73W | 35 | 20:40 | 1346 | HOU | Mon |
SAN SMF LAS MDW SDF | 611 | 20:40 | 461 | 73W | 35 | 21:15 | 611 | ORF | Mon |
HOU DAL MCI MDW FLL AUS | 1636 | 21:25 | 227 | 73W | 30 | 21:55 | 1636 | SDF | Mon |
MDW | 3961 | 23:15 | 479 | 73W | Mon | ||||
RNO LAS BWI | 3745 | 23:20 | 440 | 73W | Mon | ||||
BWI MSY DAL HOU | 51 | 23:30 | 100 | 733 | Mon | ||||
BUR PHX LAS | 263 | 23:50 | 287 | 73W | Mon | ||||
DEN | 760 | 00:45 | 448 | 73W | Mon |
<TBODY>
</TBODY><COLGROUP><COL><COL span=7><COL><COL></COLGROUP>