find the connecting flights based on criteria

[guilherme88]

Hi all,

I have been struggling with the following for a number of days now.

I want to be able to retrieve the flights that can be connected with Y flight.
Basically..

I have Flight nr AA0001 "Y" and based on a departure and arrival list and the minimum connecting time, i want to see which "X" flights give enought time to connect with AA0001. I have tried index&sumproduct and index&match&countif but nothing seems to work.

Is anybody able to help me?

Thanks

Gui

 

[guilherme88]

See below some data, which might be able to explain better what i mean..
The issue is that the data repeats itself depending on the day of the season which could be 10march, 03august, 02sep or any other date. I want to retrieve the possible connection options so for the case below, if i have a flight departing at 05:45 the flight AA100 would connect with. but if i have a flight leaving at 05:20 AA100 would not connect with it.

Please help

FLIGHT NR ARR/DEP ACTUAL TIME CONNECTION TIME DAYS OF WEEK SEASON

AA

100

Arrival

04:55

05:40

Sun

Winter

AA

100

Arrival

04:55

05:40

Mon

Tue

Thu

Sat

Winter

AA

100

Arrival

04:55

05:40

Wed

Fri

Sun

Winter

AA

100

Arrival

04:55

05:40

Mon

Tue

Thu

Sat

Winter

AA

100

Arrival

04:55

05:40

Mon

Tue

Thu

Sat

Winter

AA

101

Departure

12:55

12:10

Mon

Wed

Fri

Sun

Winter

AA

101

Departure

12:55

12:10

Tue

Thu

Sat

Winter

AA

101

Departure

12:55

12:10

Mon

Wed

Fri

Sun

Winter

AA

101

Departure

12:55

12:10

Mon

Wed

Fri

Sun

Winter

<colgroup><col><col><col><col span="2"><col span="8"></colgroup><tbody>
</tbody>

 

[nemmi69]

Are you doing this in VBa or formulas?

 

[guilherme88]

with formulas. I can't use VBA.

 

[guilherme88]

Can somebody help please?

 

[nemmi69]

Sorry I am no 'guru' with the standard sheets. Suspect it would require using MATCH and INDEX