Sequencing or Rotation in Excel

[sherifhanna82]

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.

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>