Schedule lookup

[Kariba]

I have been given a schedule below and need to link arrival times to next departure time. ie 10:05 arrival linking to next 11:15 departure or 17:40 linking to next 18:55.

This is filtered on one example - number 54 which is the only common thread between them but linked rows could be hundreds of rows apart as it's in time order. I need to be able to reliably move up column B until it finds a match and then return the value in column G. Tried a few ways but not getting required result.

Any suggestions appreciated.

Book1
	A	B	C	D	E	F	G
1	Aircraft Type	Aircraft Identifier	Flight	Departure Airport	Departure Time	Arrival Airport	Arrival Time
2	737	54	4522	ATL	04:35:00	MCO	07:00
3	737	54	4523	MCO	07:35:00	ATL	10:05
4	737	54	4578	ATL	11:15:00	TPA	14:10
5	737	54	4579	TPA	14:45:00	ATL	17:40
6	737	54	5000	ATL	18:55:00	TPA	20:35
Sheet1

 

[Peter_SSs]

There is some confusion for me about your data. It seems that your Departure Times are times only (decimal number)

.. but your Arrival Times are a date and a time ( whole number + decimal) but formatted to show the time portion only

Can you confirm that is the case with your real data, or else please clarify what the actual situation is?

Also are you
- given an arrival time and want to look up the next departure time, or
- given a departure time and want to look up the previous arrival time?

 

[Kariba]

Sometimes it comes with date and time, but just need time format for all of it. For each departure, I need to find a previous arrival time for each aircraft

 

[Peter_SSs]

Is this what you are looking for then?

23 09 05.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Aircraft Type	Aircraft Identifier	Flight	Departure Airport	Departure Time	Arrival Airport	Arrival Time		Identifier	Arrival	Departure
2	737	54	4522	ATL	04:35:00	MCO	07:00		54	10:05	11:15:00
3	737	54	4523	MCO	07:35:00	ATL	10:05		54	17:40	18:55:00
4		48
5		22
6	737	54	4578	ATL	11:15:00	TPA	14:10
7		77
8	737	54	4579	TPA	14:45:00	ATL	17:40
9		1
10		2
11		3
12	737	54	5000	ATL	18:55:00	TPA	20:35
13
14
Arrival Departure

Cell Formulas
Range	Formula
J2:J3	J2	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Identifier]=I2)*(AT<K2),"")))

 

[Kariba]

Certainly looks like it'll work. I'll paste into main table to test and will let you know

 

[Kariba]

Unfortunately not working when I moved it over. Not sure why as I can see anything different.

 

[Peter_SSs]

Unfortunately not working when I moved it over.

What does "not working" mean (error? wrong result? crash Excel? something else?)

Did you adjust the table name &/or table column headings to match?

What about you post a small sample with XL2BB (any sensitive data removed or disguised) so that we can see your actual data/layout and the formula "not working"?

 

[Kariba]

Error messages. I had changed the headings etc to match. I've made further changes now though. Highlighted below examples of the data I'm trying to match together. Thanks.

Crew Catering Schedule issued - 04 Sep 2023.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Flight Date	Loading Airport	Aircraft Type Code	Aircraft Registration Identifier	Flight Number	Departure Airport	Departure Time	Arrival Airport	Arrival Time	RouteID							First Departure Airport	Passengers Booked	Carrier Code			Identifier	Arrival
2	07/09/2023			18			07/09/2023 05:45		07:40:00													18	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G2),"")))
3	07/09/2023			20			07/09/2023 06:00		07:30:00													20	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G3),"")))
4	07/09/2023			13			07/09/2023 08:00		09:10:00													13	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G4),"")))
5	07/09/2023			18			07/09/2023 08:10		10:00:00													18	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G5),"")))
6	07/09/2023			20			07/09/2023 08:25		10:45:00													20	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G6),"")))
7	07/09/2023			13			07/09/2023 09:40		11:25:00													13	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G7),"")))
8	07/09/2023			18			07/09/2023 10:50		13:50:00													18	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G8),"")))
9	07/09/2023			20			07/09/2023 11:20		13:55:00													20	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G9),"")))
10	07/09/2023			13			07/09/2023 11:55		13:35:00													13	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G10),"")))
11	07/09/2023			13			07/09/2023 14:20		15:40:00													13	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G11),"")))
12	07/09/2023			18			07/09/2023 14:20		17:15:00													18	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G12),"")))
13	07/09/2023			13			07/09/2023 16:10		17:25:00													13	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G13),"")))
14	07/09/2023			20			07/09/2023 16:15		19:05:00													20	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G14),"")))
15	07/09/2023			13			07/09/2023 17:55		19:25:00													13	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G15),"")))
16	07/09/2023			18			07/09/2023 18:00		19:40:00													18	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G16),"")))
17	07/09/2023			20			07/09/2023 19:35		22:15:00													20	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G17),"")))
18	07/09/2023			13			07/09/2023 19:55		21:15:00													13	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G18),"")))
19	07/09/2023			18			07/09/2023 20:10		21:45:00													18	=LET(AT,MOD(Table1[Arrival Time],1),MAX(FILTER(AT,(Table1[Aircraft Registration Identifier]=V3)*(AT<G19),"")))
Catering Schedule

Cell Formulas
Range	Formula
V2:V19	V2	=D2

 

[Peter_SSs]

Error messages.

Thanks. What error messages?

I've made further changes now though.

You certainly have.

1. This sample data is not in a formal Excel table as the earlier sample data appeared to be. Is it in a formal table or not?
   If it is a formal table
   • What is the table name?
   • What columns are actually included in the table?
     
2. Previously, the Departure Time underlying cell value was a time only. Now it is a date and time, like the Arrival Time column. Can you confirm that both Arrival Time and Departure Time columns contain date and time & it is just the formatting that makes them display differently to each other?
   
   
3. Can you confirm that for that latest sample data, you would not be expecting any result for the first row since there is no Arrival Time before that first Departure Time of 05:45 on 07/09/2023 for Identifier 18? If you are expecting a result other than "", what result would it be and why?

 

[Kariba]

It's not in a formal table when it is received. We do some editing/formatting once we get it.
Arrival and departure contains both date and time although date is not normally relevant for us
There would be no result expected on first row and can remain blank.

Thanks