# VBA- To Merge two sets based on a few criterias



## shansakhi (Dec 26, 2022)

Dear All,
Need your assistance for below.

Khalid_COnnectivity_Working.xlsxABCDEFGHIJKLMNOPQRSTU1Data Set 1Data Set 22DepDOWFrqAlnFLt NoDept StaDept TimeArvl StaArvl TimeTrafficDepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTraffic330-Dec-22Sun7VO102LHR20:05DEL07:15Inbound30-Dec-22Sun7VO331DEL14:10HYD18:25Outbound431-Dec-22Sun7VO116FRA10:05DEL22:25Inbound30-Dec-22Sun7VO551DEL02:45MAa07:05Outbound530-Dec-22Sun7VO124MUC21:45DEL06:50Inbound30-Dec-22Sun7VO125DEL11:50SXR18:50Outbound631-Dec-22Sun7VO132CDG21:10DEL07:10Inbound1-Jan-23Sun7VO409DEL14:45CDG19:25Outbound730-Dec-22Sun7VO145DEL08:10CHR18:20Outbound830-Dec-22Sun7VO155DEL13:00ZRO19:05Outbound930-Dec-22Sun7VO171DEL11:35ISO19:00Outbound1031-Dec-22Sun7VO209DEL23:30FRS06:40Outbound1112OutPut13Need to merge these two data sets with below criteria's14> 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 needed16> 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 hours1718Examples19DepDOWFrqAlnFLt NoDept StaDept TimeArvl StaArvl TimeTrafficConx TimeDepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTraffic2030-Dec-22Sun7VO102LHR20:05DEL07:15Inbound02:0030-Dec-22Sun7VO331DEL09:15HYD16:15Outbound2130-Dec-22Sun7VO102LHR20:05DEL07:15Inbound00:5530-Dec-22Sun7VO551DEL08:10MAA04:10Outbound2230-Dec-22Sun7VO124MUC21:45DEL06:50Inbound05:0030-Dec-22Sun7VO125DEL11:50SXR18:50Outbound2330-Dec-22Sun7VO124MUC21:45DEL06:50Inbound01:2030-Dec-22Sun7VO145DEL08:10CHR18:20Outbound2431-Dec-22Sun7VO132CDG21:10DEL07:10Inbound05:5030-Dec-22Sun7VO155DEL13:00ZRO19:05Outbound2531-Dec-22Sun7VO132CDG21:10DEL07:10Inbound04:2530-Dec-22Sun7VO171DEL11:35ISO19:00Outbound2631-Dec-22Sun7VO116FRA10:05DEL22:25Inbound04:001-Jan-23Sun7VO409DEL02:25BOM04:25Outbound2731-Dec-22Sun7VO116FRA10:05DEL22:25Inbound01:0531-Dec-22Sun7VO209DEL23:30FRS06:40Outbound28Sheet3


----------



## Flashbond (Dec 26, 2022)

Did you mean just the opposite?


----------



## Flashbond (Dec 26, 2022)

Ok, I understand what you mean. You want to list the second table's departures according to first table's arrivals. But is this data wrong?


----------



## shansakhi (Dec 26, 2022)

Thank you.
I apologize! Please find the updated data.

Khalid_COnnectivity_Working.xlsxABCDEFGHIJKLMNOPQRSTUV1Data Set 1Data Set 22DepDOWFrqAlnFLt NoDept StaDept TimeArvl StaArvl TimeTrafficDepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTraffic330-Dec-22Sun7VO102LHR20:05DEL07:15Inbound30-Dec-22Sun7VO331DEL09:15HYD16:15Outbound431-Dec-22Sun7VO116FRA10:05DEL22:25Inbound30-Dec-22Sun7VO551DEL08:10MAA11:10Outbound530-Dec-22Sun7VO124MUC21:45DEL06:50Inbound30-Dec-22Sun7VO125DEL11:50SXR18:50Outbound631-Dec-22Sun7VO132CDG21:10DEL07:10Inbound1-Jan-23Sun7VO409DEL02:25BOM04:25Outbound730-Dec-22Sun7VO145DEL08:10CHR18:20Outbound830-Dec-22Sun7VO155DEL13:00ZRO19:05Outbound930-Dec-22Sun7VO171DEL11:35ISO19:00Outbound1031-Dec-22Sun7VO209DEL23:30FRS06:40Outbound1112OutPut13Need to merge these two data sets with below criteria's14> 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 needed16> 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 hours1718Examples19DepDOWFrqAlnFLt NoDept StaDept TimeArvl StaArvl TimeTrafficConx TimeDepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTraffic2030-Dec-22Sun7VO102LHR20:05DEL07:15Inbound02:0030-Dec-22Sun7VO331DEL09:15HYD16:15Outbound2130-Dec-22Sun7VO102LHR20:05DEL07:15Inbound00:5530-Dec-22Sun7VO551DEL08:10MAA11:10Outbound2230-Dec-22Sun7VO124MUC21:45DEL06:50Inbound05:0030-Dec-22Sun7VO125DEL11:50SXR18:50Outbound2330-Dec-22Sun7VO124MUC21:45DEL06:50Inbound01:2030-Dec-22Sun7VO145DEL08:10CHR18:20Outbound2431-Dec-22Sun7VO132CDG21:10DEL07:10Inbound05:5030-Dec-22Sun7VO155DEL13:00ZRO19:05Outbound2531-Dec-22Sun7VO132CDG21:10DEL07:10Inbound04:2530-Dec-22Sun7VO171DEL11:35ISO19:00Outbound2631-Dec-22Sun7VO116FRA10:05DEL22:25Inbound04:001-Jan-23Sun7VO409DEL02:25BOM04:25Outbound2731-Dec-22Sun7VO116FRA10:05DEL22:25Inbound01:0531-Dec-22Sun7VO209DEL23:30FRS06:40Outbound28Sheet3


----------



## Flashbond (Dec 26, 2022)

I still don't understand. This flight arrives at 07.15 on 31-Dec-22. How can you connect it to flight on 30-Dec-22?


----------



## shansakhi (Dec 26, 2022)

It's my bad... sorry .. Made changes to data.

Khalid_COnnectivity_Working.xlsxABCDEFGHIJKLMNOPQRSTUV1Data Set 1Data Set 22DepDOWFrqAlnFLt NoDept StaDept TimeArvl StaArvl TimeTrafficDepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTraffic330-Dec-22Sun7VO102LHR20:05DEL07:15Inbound30-Dec-22Sun7VO331DEL09:15HYD16:15Outbound431-Dec-22Sun7VO116FRA10:05DEL22:25Inbound30-Dec-22Sun7VO551DEL08:10MAA04:10Outbound530-Dec-22Sun7VO124MUC21:45DEL06:50Inbound30-Dec-22Sun7VO125DEL11:50SXR18:50Outbound631-Dec-22Sun7VO132CDG21:10DEL07:10Inbound1-Jan-23Sun7VO409DEL14:45CDG19:25Outbound730-Dec-22Sun7VO145DEL08:10CHR18:20Outbound831-Dec-22Sun7VO155DEL13:00ZRO19:05Outbound931-Dec-22Sun7VO171DEL11:35ISO19:00Outbound1031-Dec-22Sun7VO209DEL23:30FRS06:40Outbound1112OutPut13Need to merge these two data sets with below criteria's14> 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 needed16> 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 hours1718Examples19DepDOWFrqAlnFLt NoDept StaDept TimeArvl StaArvl TimeTrafficConx TimeDepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTraffic2030-Dec-22Sun7VO102LHR20:05DEL07:15Inbound02:0030-Dec-22Sun7VO331DEL09:15HYD16:15Outbound2130-Dec-22Sun7VO102LHR20:05DEL07:15Inbound00:5530-Dec-22Sun7VO551DEL08:10MAA04:10Outbound2230-Dec-22Sun7VO124MUC21:45DEL06:50Inbound05:0030-Dec-22Sun7VO125DEL11:50SXR18:50Outbound2330-Dec-22Sun7VO124MUC21:45DEL06:50Inbound01:2030-Dec-22Sun7VO145DEL08:10CHR18:20Outbound2431-Dec-22Sun7VO132CDG21:10DEL07:10Inbound05:5031-Dec-22Sun7VO155DEL13:00ZRO19:05Outbound2531-Dec-22Sun7VO132CDG21:10DEL07:10Inbound04:2531-Dec-22Sun7VO171DEL11:35ISO19:00Outbound2631-Dec-22Sun7VO116FRA10:05DEL22:25Inbound04:001-Jan-23Sun7VO409DEL02:25BOM04:25Outbound2731-Dec-22Sun7VO116FRA10:05DEL22:25Inbound01:0531-Dec-22Sun7VO209DEL23:30FRS06:40Outbound28Sheet3


----------



## Flashbond (Dec 26, 2022)

No, it still doesn't make any sense? Flight no. 102 arrives on 31-Dec-22 in the morning. How come can it be connected to flight no. 331? It is on 30-Dec-22.

Anyway, please find the sample file using Power Query. I made modifications according to my logic.


			https://file.io/a6cS9IAAg79d


----------



## jdellasala (Dec 26, 2022)

First, column E - Data Set 1 Column's name is "FLt No", and the corresponding column in Data Set 2 - Column P is "Flt Num". It's a minor inconsistency, but causes problems with Power Query.
@Flashbond - the link says "The transfer you requested has been deleted.".
Also, with four flights in DS1, and 8 flights in DS2, I'm not clear on how the final table has 4 flights but 8 rows.


----------



## jdellasala (Dec 26, 2022)

So using Power Query I pulled the two data sets in and worked out the arrival and departure date times as well as the travel time:
Book1ABCDEFGHIJKLM1DepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTrafficDepart DateTimeArrival DateTimeTravel Time212/30/2022Sun7VO102LHR08:05:00 PMDEL07:15:00 AMInbound12/30/22 8:05 PM12/31/22 7:15 AM0.11:10:00312/31/2022Sun7VO116FRA10:05:00 AMDEL10:25:00 PMInbound12/31/22 10:05 AM12/31/22 10:25 PM0.12:20:00412/30/2022Sun7VO124MUC09:45:00 PMDEL06:50:00 AMInbound12/30/22 9:45 PM12/31/22 6:50 AM0.09:05:00512/31/2022Sun7VO132CDG09:10:00 PMDEL07:10:00 AMInbound12/31/22 9:10 PM1/1/23 7:10 AM0.10:00:00DS1Flights
Book1ABCDEFGHIJKLM1DepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTrafficDepart DateTimeArrival DateTimeTravel Time212/30/2022Sun7VO331DEL09:15:00 AMHYD04:15:00 PMOutbound12/30/22 9:15 AM12/30/22 4:15 PM0.07:00:00312/30/2022Sun7VO551DEL08:10:00 AMMAA11:10:00 AMOutbound12/30/22 8:10 AM12/30/22 11:10 AM0.03:00:00412/30/2022Sun7VO125DEL11:50:00 AMSXR06:50:00 PMOutbound12/30/22 11:50 AM12/30/22 6:50 PM0.07:00:00501/01/2023Sun7VO409DEL02:25:00 AMBOM04:25:00 AMOutbound1/1/23 2:25 AM1/1/23 4:25 AM0.02:00:00612/30/2022Sun7VO145DEL08:10:00 AMCHR06:20:00 PMOutbound12/30/22 8:10 AM12/30/22 6:20 PM0.10:10:00712/30/2022Sun7VO155DEL01:00:00 PMZRO07:05:00 PMOutbound12/30/22 1:00 PM12/30/22 7:05 PM0.06:05:00812/30/2022Sun7VO171DEL11:35:00 AMISO07:00:00 PMOutbound12/30/22 11:35 AM12/30/22 7:00 PM0.07:25:00912/31/2022Sun7VO209DEL11:30:00 PMFRS06:40:00 AMOutbound12/31/22 11:30 PM1/1/23 6:40 AM0.07:10:00DS2Flights
But in attempting to FILTER the DS2 flights available after the DS1 flight arrivals and not include already taken flights, only flights 102 and 116 match flights leaving after their arrival - flights 209 and 409 respectively. The two remaining flights - 124 and 132 - both arrive on 12/31/2022, but there are no DS2 flights after 12/30/2022.
Book1ABCDEFGHIJKLMNO1DepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTrafficDepart DateTimeArrival DateTimeTravel TimeNext DS2 FlightConx Flt212/30/2022Sun7VO102LHR08:05:00 PMDEL07:15:00 AMInbound12/30/22 8:05 PM12/31/22 7:15 AM0.11:10:0012/31/22 11:30 PM209312/31/2022Sun7VO116FRA10:05:00 AMDEL10:25:00 PMInbound12/31/22 10:05 AM12/31/22 10:25 PM0.12:20:001/1/23 2:25 AM409412/30/2022Sun7VO124MUC09:45:00 PMDEL06:50:00 AMInbound12/30/22 9:45 PM12/31/22 6:50 AM0.09:05:00#CALC!#CALC!512/31/2022Sun7VO132CDG09:10:00 PMDEL07:10:00 AMInbound12/31/22 9:10 PM1/1/23 7:10 AM0.10:00:00#CALC!#CALC!DS1FlightsCell FormulasRangeFormulaN2N2=MIN(FILTER(DS2Flights[Depart DateTime],(DS2Flights[Depart DateTime]>=[@[Arrival DateTime]])))O2:O5O2=XLOOKUP([@[Next DS2 Flight]],DS2Flights[Depart DateTime],DS2Flights[Flt Num])N3,N5N3=MIN(FILTER(DS2Flights[Depart DateTime],(DS2Flights[Depart DateTime]>=[@[Arrival DateTime]])*(DS2Flights[Depart DateTime]<>N2)))N4N4=MIN(FILTER(DS2Flights[Depart DateTime],(DS2Flights[Depart DateTime]>=[@[Arrival DateTime]])*(DS2Flights[Depart DateTime]<>N2)*(DS2Flights[Depart DateTime]<>N3)))
Am I just not understanding what the end goal is?


----------



## shansakhi (Dec 27, 2022)

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. 
Book2ABCDEFGHIJKLMNOPQRSTUVWXY1Data Set 1Data Set 22DepDOWFrqAlnFLt NoDept StaDepart DateTimeArrival DateTimeDept TimeArvl StaArvl TimeTrafficDepDOWFrqAlnFlt NumDept StaDepart DateTimeArrival DateTimeDept TimeArvl StaArvl TimeTraffic330-Dec-22Sun7VO102LHR12/30/22 14:0512/30/22 22:1514:05DEL22:15Inbound31-Dec-22Sun7VO331DEL12/31/22 4:1512/31/22 8:1504:15HYD08:15Outbound431-Dec-22Sun7VO116FRA12/31/22 10:0512/31/22 22:2510:05DEL22:25Inbound31-Dec-22Sun7VO551DEL12/31/22 3:1012/31/22 4:1003:10MAA10:10Outbound530-Dec-22Sun7VO124MUC12/30/22 21:4512/31/22 6:5021:45DEL06:50Inbound31-Dec-22Sun7VO125DEL12/31/22 11:5012/31/22 18:5011:50SXR14:50Outbound631-Dec-22Sun7VO132CDG12/31/22 21:101/1/23 7:1021:10DEL07:10Inbound1-Jan-23Sun7VO409DEL1/1/23 12:451/1/23 19:2512:45BOM19:25Outbound730-Dec-22Sun7VO145DEL12/30/22 23:1512/31/22 3:2023:15CHR03:20Outbound831-Dec-22Sun7VO155DEL12/31/22 12:5012/31/22 19:0512:50ZRO15:05Outbound931-Dec-22Sun7VO171DEL12/31/22 11:3512/31/22 19:0011:35ISO14:00Outbound1031-Dec-22Sun7VO209DEL12/31/22 23:301/1/23 6:4023:30FRS06:40Outbound1112OutPut13Need to merge these two data sets with below criteria's14> 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 needed16> 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 hours1718Examples19DepDOWFrqAlnFLt NoDept StaDepart DateTimeArrival DateTimeDept TimeArvl StaArvl TimeTrafficConx TimeDepDOWFrqAlnFlt NumDept StaDepart DateTimeArrival DateTimeDept TimeArvl StaArvl TimeTraffic2030-Dec-22Sun7VO102LHR12/30/22 14:0512/30/22 22:1514:05DEL22:15Inbound06:0031-Dec-22Sun7VO331DEL12/31/22 4:1512/31/22 8:1504:15HYD08:15Outbound2130-Dec-22Sun7VO102LHR12/30/22 14:0512/30/22 22:1514:05DEL22:15Inbound04:5531-Dec-22Sun7VO551DEL12/31/22 3:1012/31/22 4:1003:10MAA10:10Outbound2230-Dec-22Sun7VO102LHR12/30/22 14:0512/30/22 22:1514:05DEL22:15Inbound01:0030-Dec-22Sun7VO145DEL12/30/22 23:1512/31/22 3:2023:15CHR03:20Outbound2331-Dec-22Sun7VO116FRA12/31/22 10:0512/31/22 22:2510:05DEL22:25Inbound01:0531-Dec-22Sun7VO209DEL12/31/22 23:301/1/23 6:4023:30FRS06:40Outbound2430-Dec-22Sun7VO124MUC12/30/22 21:4512/31/22 6:5021:45DEL06:50Inbound05:0031-Dec-22Sun7VO125DEL12/31/22 11:5012/31/22 18:5011:50SXR14:50Outbound2530-Dec-22Sun7VO124MUC12/30/22 21:4512/31/22 6:5021:45DEL06:50Inbound04:4531-Dec-22Sun7VO171DEL12/31/22 11:3512/31/22 19:0011:35ISO14:00Outbound2630-Dec-22Sun7VO124MUC12/30/22 21:4512/31/22 6:5021:45DEL06:50Inbound06:0031-Dec-22Sun7VO155DEL12/31/22 12:5012/31/22 19:0512:50ZRO15:05Outbound2731-Dec-22Sun7VO132CDG12/31/22 21:101/1/23 7:1021:10DEL07:10Inbound05:351-Jan-23Sun7VO409DEL1/1/23 12:451/1/23 19:2512:45BOM19:25Outbound2829Sheet3Cell FormulasRangeFormulaM20:M27M20=T20-H20


----------



## shansakhi (Dec 26, 2022)

Dear All,
Need your assistance for below.

Khalid_COnnectivity_Working.xlsxABCDEFGHIJKLMNOPQRSTU1Data Set 1Data Set 22DepDOWFrqAlnFLt NoDept StaDept TimeArvl StaArvl TimeTrafficDepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTraffic330-Dec-22Sun7VO102LHR20:05DEL07:15Inbound30-Dec-22Sun7VO331DEL14:10HYD18:25Outbound431-Dec-22Sun7VO116FRA10:05DEL22:25Inbound30-Dec-22Sun7VO551DEL02:45MAa07:05Outbound530-Dec-22Sun7VO124MUC21:45DEL06:50Inbound30-Dec-22Sun7VO125DEL11:50SXR18:50Outbound631-Dec-22Sun7VO132CDG21:10DEL07:10Inbound1-Jan-23Sun7VO409DEL14:45CDG19:25Outbound730-Dec-22Sun7VO145DEL08:10CHR18:20Outbound830-Dec-22Sun7VO155DEL13:00ZRO19:05Outbound930-Dec-22Sun7VO171DEL11:35ISO19:00Outbound1031-Dec-22Sun7VO209DEL23:30FRS06:40Outbound1112OutPut13Need to merge these two data sets with below criteria's14> 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 needed16> 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 hours1718Examples19DepDOWFrqAlnFLt NoDept StaDept TimeArvl StaArvl TimeTrafficConx TimeDepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTraffic2030-Dec-22Sun7VO102LHR20:05DEL07:15Inbound02:0030-Dec-22Sun7VO331DEL09:15HYD16:15Outbound2130-Dec-22Sun7VO102LHR20:05DEL07:15Inbound00:5530-Dec-22Sun7VO551DEL08:10MAA04:10Outbound2230-Dec-22Sun7VO124MUC21:45DEL06:50Inbound05:0030-Dec-22Sun7VO125DEL11:50SXR18:50Outbound2330-Dec-22Sun7VO124MUC21:45DEL06:50Inbound01:2030-Dec-22Sun7VO145DEL08:10CHR18:20Outbound2431-Dec-22Sun7VO132CDG21:10DEL07:10Inbound05:5030-Dec-22Sun7VO155DEL13:00ZRO19:05Outbound2531-Dec-22Sun7VO132CDG21:10DEL07:10Inbound04:2530-Dec-22Sun7VO171DEL11:35ISO19:00Outbound2631-Dec-22Sun7VO116FRA10:05DEL22:25Inbound04:001-Jan-23Sun7VO409DEL02:25BOM04:25Outbound2731-Dec-22Sun7VO116FRA10:05DEL22:25Inbound01:0531-Dec-22Sun7VO209DEL23:30FRS06:40Outbound28Sheet3


----------



## jdellasala (Dec 27, 2022)

shansakhi said:


> 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.
> Book2ABCDEFGHIJKLMNOPQRSTUVWXY1Data Set 1Data Set 22DepDOWFrqAlnFLt NoDept StaDepart DateTimeArrival DateTimeDept TimeArvl StaArvl TimeTrafficDepDOWFrqAlnFlt NumDept StaDepart DateTimeArrival DateTimeDept TimeArvl StaArvl TimeTraffic330-Dec-22Sun7VO102LHR12/30/22 14:0512/30/22 22:1514:05DEL22:15Inbound31-Dec-22Sun7VO331DEL12/31/22 4:1512/31/22 8:1504:15HYD08:15Outbound431-Dec-22Sun7VO116FRA12/31/22 10:0512/31/22 22:2510:05DEL22:25Inbound31-Dec-22Sun7VO551DEL12/31/22 3:1012/31/22 4:1003:10MAA10:10Outbound530-Dec-22Sun7VO124MUC12/30/22 21:4512/31/22 6:5021:45DEL06:50Inbound31-Dec-22Sun7VO125DEL12/31/22 11:5012/31/22 18:5011:50SXR14:50Outbound631-Dec-22Sun7VO132CDG12/31/22 21:101/1/23 7:1021:10DEL07:10Inbound1-Jan-23Sun7VO409DEL1/1/23 12:451/1/23 19:2512:45BOM19:25Outbound730-Dec-22Sun7VO145DEL12/30/22 23:1512/31/22 3:2023:15CHR03:20Outbound831-Dec-22Sun7VO155DEL12/31/22 12:5012/31/22 19:0512:50ZRO15:05Outbound931-Dec-22Sun7VO171DEL12/31/22 11:3512/31/22 19:0011:35ISO14:00Outbound1031-Dec-22Sun7VO209DEL12/31/22 23:301/1/23 6:4023:30FRS06:40Outbound1112OutPut13Need to merge these two data sets with below criteria's14> 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 needed16> 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 hours1718Examples19DepDOWFrqAlnFLt NoDept StaDepart DateTimeArrival DateTimeDept TimeArvl StaArvl TimeTrafficConx TimeDepDOWFrqAlnFlt NumDept StaDepart DateTimeArrival DateTimeDept TimeArvl StaArvl TimeTraffic2030-Dec-22Sun7VO102LHR12/30/22 14:0512/30/22 22:1514:05DEL22:15Inbound06:0031-Dec-22Sun7VO331DEL12/31/22 4:1512/31/22 8:1504:15HYD08:15Outbound2130-Dec-22Sun7VO102LHR12/30/22 14:0512/30/22 22:1514:05DEL22:15Inbound04:5531-Dec-22Sun7VO551DEL12/31/22 3:1012/31/22 4:1003:10MAA10:10Outbound2230-Dec-22Sun7VO102LHR12/30/22 14:0512/30/22 22:1514:05DEL22:15Inbound01:0030-Dec-22Sun7VO145DEL12/30/22 23:1512/31/22 3:2023:15CHR03:20Outbound2331-Dec-22Sun7VO116FRA12/31/22 10:0512/31/22 22:2510:05DEL22:25Inbound01:0531-Dec-22Sun7VO209DEL12/31/22 23:301/1/23 6:4023:30FRS06:40Outbound2430-Dec-22Sun7VO124MUC12/30/22 21:4512/31/22 6:5021:45DEL06:50Inbound05:0031-Dec-22Sun7VO125DEL12/31/22 11:5012/31/22 18:5011:50SXR14:50Outbound2530-Dec-22Sun7VO124MUC12/30/22 21:4512/31/22 6:5021:45DEL06:50Inbound04:4531-Dec-22Sun7VO171DEL12/31/22 11:3512/31/22 19:0011:35ISO14:00Outbound2630-Dec-22Sun7VO124MUC12/30/22 21:4512/31/22 6:5021:45DEL06:50Inbound06:0031-Dec-22Sun7VO155DEL12/31/22 12:5012/31/22 19:0512:50ZRO15:05Outbound2731-Dec-22Sun7VO132CDG12/31/22 21:101/1/23 7:1021:10DEL07:10Inbound05:351-Jan-23Sun7VO409DEL1/1/23 12:451/1/23 19:2512:45BOM19:25Outbound2829Sheet3Cell FormulasRangeFormulaM20:M27M20=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?


----------



## Flashbond (Dec 28, 2022)

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.









						Sample.xlsx
					

1 file sent via WeTransfer, the simplest way to send your files around the world




					we.tl


----------



## shansakhi (Jan 4, 2023)

jdellasala said:


> 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 (Jan 4, 2023)

Flashbond said:


> 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.
> 
> 
> 
> ...


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.


----------

