VBA- To Merge two sets based on a few criterias

[shansakhi]

Dear All,
Need your assistance for below.

Khalid_COnnectivity_Working.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Data Set 1											Data Set 2
2	Dep	DOW	Frq	Aln	FLt No	Dept Sta	Dept Time	Arvl Sta	Arvl Time	Traffic		Dep	DOW	Frq	Aln	Flt Num	Dept Sta	Dept Time	Arvl Sta	Arvl Time	Traffic
3	30-Dec-22	Sun	7	VO	102	LHR	20:05	DEL	07:15	Inbound		30-Dec-22	Sun	7	VO	331	DEL	14:10	HYD	18:25	Outbound
4	31-Dec-22	Sun	7	VO	116	FRA	10:05	DEL	22:25	Inbound		30-Dec-22	Sun	7	VO	551	DEL	02:45	MAa	07:05	Outbound
5	30-Dec-22	Sun	7	VO	124	MUC	21:45	DEL	06:50	Inbound		30-Dec-22	Sun	7	VO	125	DEL	11:50	SXR	18:50	Outbound
6	31-Dec-22	Sun	7	VO	132	CDG	21:10	DEL	07:10	Inbound		1-Jan-23	Sun	7	VO	409	DEL	14:45	CDG	19:25	Outbound
7												30-Dec-22	Sun	7	VO	145	DEL	08:10	CHR	18:20	Outbound
8												30-Dec-22	Sun	7	VO	155	DEL	13:00	ZRO	19:05	Outbound
9												30-Dec-22	Sun	7	VO	171	DEL	11:35	ISO	19:00	Outbound
10												31-Dec-22	Sun	7	VO	209	DEL	23:30	FRS	06:40	Outbound
11
12	OutPut
13	Need to merge these two data sets with below criteria's
14	> If difference between Arvl Time (Data set 1) and Dept Time (Data Set 2) ragning from 55 mins to 06 hours (Conx Time)
15	> we shouold able to change the above differnec range if needed
16	> If Arvl Time (Data set 1) is late in the night then the connection should be taken for next DEP day considering 55 Mins to 06 hours
17
18	Examples
19	Dep	DOW	Frq	Aln	FLt No	Dept Sta	Dept Time	Arvl Sta	Arvl Time	Traffic	Conx Time	Dep	DOW	Frq	Aln	Flt Num	Dept Sta	Dept Time	Arvl Sta	Arvl Time	Traffic
20	30-Dec-22	Sun	7	VO	102	LHR	20:05	DEL	07:15	Inbound	02:00	30-Dec-22	Sun	7	VO	331	DEL	09:15	HYD	16:15	Outbound
21	30-Dec-22	Sun	7	VO	102	LHR	20:05	DEL	07:15	Inbound	00:55	30-Dec-22	Sun	7	VO	551	DEL	08:10	MAA	04:10	Outbound
22	30-Dec-22	Sun	7	VO	124	MUC	21:45	DEL	06:50	Inbound	05:00	30-Dec-22	Sun	7	VO	125	DEL	11:50	SXR	18:50	Outbound
23	30-Dec-22	Sun	7	VO	124	MUC	21:45	DEL	06:50	Inbound	01:20	30-Dec-22	Sun	7	VO	145	DEL	08:10	CHR	18:20	Outbound
24	31-Dec-22	Sun	7	VO	132	CDG	21:10	DEL	07:10	Inbound	05:50	30-Dec-22	Sun	7	VO	155	DEL	13:00	ZRO	19:05	Outbound
25	31-Dec-22	Sun	7	VO	132	CDG	21:10	DEL	07:10	Inbound	04:25	30-Dec-22	Sun	7	VO	171	DEL	11:35	ISO	19:00	Outbound
26	31-Dec-22	Sun	7	VO	116	FRA	10:05	DEL	22:25	Inbound	04:00	1-Jan-23	Sun	7	VO	409	DEL	02:25	BOM	04:25	Outbound
27	31-Dec-22	Sun	7	VO	116	FRA	10:05	DEL	22:25	Inbound	01:05	31-Dec-22	Sun	7	VO	209	DEL	23:30	FRS	06:40	Outbound
28
Sheet3

 

[jdellasala]

Thank you.
The purpose of this data is.. If I select any flight number / Dept Sta and date from Data Set 1 then I should get the connecting flights from Data Set 2 corresponding to the selected details of Data Set 1. In actual data sets have more than 5000 rows. for example purpose we have taken a few rows.

Now hope data is meaningful.

Book2
	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
1	Data Set 1													Data Set 2
2	Dep	DOW	Frq	Aln	FLt No	Dept Sta	Depart DateTime	Arrival DateTime	Dept Time	Arvl Sta	Arvl Time	Traffic		Dep	DOW	Frq	Aln	Flt Num	Dept Sta	Depart DateTime	Arrival DateTime	Dept Time	Arvl Sta	Arvl Time	Traffic
3	30-Dec-22	Sun	7	VO	102	LHR	12/30/22 14:05	12/30/22 22:15	14:05	DEL	22:15	Inbound		31-Dec-22	Sun	7	VO	331	DEL	12/31/22 4:15	12/31/22 8:15	04:15	HYD	08:15	Outbound
4	31-Dec-22	Sun	7	VO	116	FRA	12/31/22 10:05	12/31/22 22:25	10:05	DEL	22:25	Inbound		31-Dec-22	Sun	7	VO	551	DEL	12/31/22 3:10	12/31/22 4:10	03:10	MAA	10:10	Outbound
5	30-Dec-22	Sun	7	VO	124	MUC	12/30/22 21:45	12/31/22 6:50	21:45	DEL	06:50	Inbound		31-Dec-22	Sun	7	VO	125	DEL	12/31/22 11:50	12/31/22 18:50	11:50	SXR	14:50	Outbound
6	31-Dec-22	Sun	7	VO	132	CDG	12/31/22 21:10	1/1/23 7:10	21:10	DEL	07:10	Inbound		1-Jan-23	Sun	7	VO	409	DEL	1/1/23 12:45	1/1/23 19:25	12:45	BOM	19:25	Outbound
7														30-Dec-22	Sun	7	VO	145	DEL	12/30/22 23:15	12/31/22 3:20	23:15	CHR	03:20	Outbound
8														31-Dec-22	Sun	7	VO	155	DEL	12/31/22 12:50	12/31/22 19:05	12:50	ZRO	15:05	Outbound
9														31-Dec-22	Sun	7	VO	171	DEL	12/31/22 11:35	12/31/22 19:00	11:35	ISO	14:00	Outbound
10														31-Dec-22	Sun	7	VO	209	DEL	12/31/22 23:30	1/1/23 6:40	23:30	FRS	06:40	Outbound
11
12	OutPut
13	Need to merge these two data sets with below criteria's
14	> If difference between Arvl Time (Data set 1) and Dept Time (Data Set 2) ragning from 55 mins to 06 hours (Conx Time)
15	> we shouold able to change the above differnec range if needed
16	> If Arvl Time (Data set 1) is late in the night then the connection should be taken for next DEP day considering 55 Mins to 06 hours
17
18	Examples
19	Dep	DOW	Frq	Aln	FLt No	Dept Sta	Depart DateTime	Arrival DateTime	Dept Time	Arvl Sta	Arvl Time	Traffic	Conx Time	Dep	DOW	Frq	Aln	Flt Num	Dept Sta	Depart DateTime	Arrival DateTime	Dept Time	Arvl Sta	Arvl Time	Traffic
20	30-Dec-22	Sun	7	VO	102	LHR	12/30/22 14:05	12/30/22 22:15	14:05	DEL	22:15	Inbound	06:00	31-Dec-22	Sun	7	VO	331	DEL	12/31/22 4:15	12/31/22 8:15	04:15	HYD	08:15	Outbound
21	30-Dec-22	Sun	7	VO	102	LHR	12/30/22 14:05	12/30/22 22:15	14:05	DEL	22:15	Inbound	04:55	31-Dec-22	Sun	7	VO	551	DEL	12/31/22 3:10	12/31/22 4:10	03:10	MAA	10:10	Outbound
22	30-Dec-22	Sun	7	VO	102	LHR	12/30/22 14:05	12/30/22 22:15	14:05	DEL	22:15	Inbound	01:00	30-Dec-22	Sun	7	VO	145	DEL	12/30/22 23:15	12/31/22 3:20	23:15	CHR	03:20	Outbound
23	31-Dec-22	Sun	7	VO	116	FRA	12/31/22 10:05	12/31/22 22:25	10:05	DEL	22:25	Inbound	01:05	31-Dec-22	Sun	7	VO	209	DEL	12/31/22 23:30	1/1/23 6:40	23:30	FRS	06:40	Outbound
24	30-Dec-22	Sun	7	VO	124	MUC	12/30/22 21:45	12/31/22 6:50	21:45	DEL	06:50	Inbound	05:00	31-Dec-22	Sun	7	VO	125	DEL	12/31/22 11:50	12/31/22 18:50	11:50	SXR	14:50	Outbound
25	30-Dec-22	Sun	7	VO	124	MUC	12/30/22 21:45	12/31/22 6:50	21:45	DEL	06:50	Inbound	04:45	31-Dec-22	Sun	7	VO	171	DEL	12/31/22 11:35	12/31/22 19:00	11:35	ISO	14:00	Outbound
26	30-Dec-22	Sun	7	VO	124	MUC	12/30/22 21:45	12/31/22 6:50	21:45	DEL	06:50	Inbound	06:00	31-Dec-22	Sun	7	VO	155	DEL	12/31/22 12:50	12/31/22 19:05	12:50	ZRO	15:05	Outbound
27	31-Dec-22	Sun	7	VO	132	CDG	12/31/22 21:10	1/1/23 7:10	21:10	DEL	07:10	Inbound	05:35	1-Jan-23	Sun	7	VO	409	DEL	1/1/23 12:45	1/1/23 19:25	12:45	BOM	19:25	Outbound
28
29
Sheet3

Cell Formulas
Range	Formula
M20:M27	M20	=T20-H20

Still makes no sense. Your example output starting at row 19 shows one flight departing multiple times. You're also using simple subtraction to determine Conx Time, but if the two date/times is more than one day, it won't be correct. In short, why are there multiple results/connections for one flight?

 

[Legacy 143009]

I attached another sample book. What it does is, it first generates all possible combinations of the first and second table. Then it filters connection times using advanced filter. It will allow you to input any time range you like.

WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free

WeTransfer is the simplest way to send your files around the world

we.tl

 

[shansakhi]

Still makes no sense. Your example output starting at row 19 shows one flight departing multiple times. You're also using simple subtraction to determine Conx Time, but if the two date/times is more than one day, it won't be correct. In short, why are there multiple results/connections for one flight?

A single flight can have multiple connections. The purpose of this report to see how many connections each flight has.
We are restricting the connections from 0:55 mins to 6 hours.

 

[shansakhi]

I attached another sample book. What it does is, it first generates all possible combinations of the first and second table. Then it filters connection times using advanced filter. It will allow you to input any time range you like.

WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free

WeTransfer is the simplest way to send your files around the world

we.tl

Thank you.. This works but the only issue is that the formula doesn't look at the restriction of connection i.e. 0:55 mins to 06:00 hours.
When we use the formula on huge range it creating different possibilities over 6 hours as well.... which unable to fit in excel.
also, for more than 24 hours connections as well the formula gives conx time within 6 hours.
e.g. Data Set1 - Flight Arriving on 31st Oct 07:15 am shows conx with 4th Nov flight Departing at 09:00 am with 01:45 conx time. In actual the conx time is much more than 6 hours.