VBA- To Merge two sets based on a few criterias

S

shansakhi

Active Member
Joined
Apr 5, 2008
Messages
276
Office Version
  1. 365
Platform
  1. Windows
Dear All,
Need your assistance for below.

Khalid_COnnectivity_Working.xlsx
ABCDEFGHIJKLMNOPQRSTU
1Data Set 1Data Set 2
2DepDOWFrqAlnFLt NoDept StaDept TimeArvl StaArvl TimeTrafficDepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTraffic
330-Dec-22Sun7VO102LHR20:05DEL07:15Inbound30-Dec-22Sun7VO331DEL14:10HYD18:25Outbound
431-Dec-22Sun7VO116FRA10:05DEL22:25Inbound30-Dec-22Sun7VO551DEL02:45MAa07:05Outbound
530-Dec-22Sun7VO124MUC21:45DEL06:50Inbound30-Dec-22Sun7VO125DEL11:50SXR18:50Outbound
631-Dec-22Sun7VO132CDG21:10DEL07:10Inbound1-Jan-23Sun7VO409DEL14:45CDG19:25Outbound
730-Dec-22Sun7VO145DEL08:10CHR18:20Outbound
830-Dec-22Sun7VO155DEL13:00ZRO19:05Outbound
930-Dec-22Sun7VO171DEL11:35ISO19:00Outbound
1031-Dec-22Sun7VO209DEL23:30FRS06:40Outbound
11
12OutPut
13Need 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
18Examples
19DepDOWFrqAlnFLt NoDept StaDept TimeArvl StaArvl TimeTrafficConx TimeDepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTraffic
2030-Dec-22Sun7VO102LHR20:05DEL07:15Inbound02:0030-Dec-22Sun7VO331DEL09:15HYD16:15Outbound
2130-Dec-22Sun7VO102LHR20:05DEL07:15Inbound00:5530-Dec-22Sun7VO551DEL08:10MAA04:10Outbound
2230-Dec-22Sun7VO124MUC21:45DEL06:50Inbound05:0030-Dec-22Sun7VO125DEL11:50SXR18:50Outbound
2330-Dec-22Sun7VO124MUC21:45DEL06:50Inbound01:2030-Dec-22Sun7VO145DEL08:10CHR18:20Outbound
2431-Dec-22Sun7VO132CDG21:10DEL07:10Inbound05:5030-Dec-22Sun7VO155DEL13:00ZRO19:05Outbound
2531-Dec-22Sun7VO132CDG21:10DEL07:10Inbound04:2530-Dec-22Sun7VO171DEL11:35ISO19:00Outbound
2631-Dec-22Sun7VO116FRA10:05DEL22:25Inbound04:001-Jan-23Sun7VO409DEL02:25BOM04:25Outbound
2731-Dec-22Sun7VO116FRA10:05DEL22:25Inbound01:0531-Dec-22Sun7VO209DEL23:30FRS06:40Outbound
28
Sheet3
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
1672044747525.png


Did you mean just the opposite?
 
Upvote 0
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?
1672046233643.png
 
Upvote 0
Thank you.
I apologize! Please find the updated data.

Khalid_COnnectivity_Working.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1Data Set 1Data Set 2
2DepDOWFrqAlnFLt NoDept StaDept TimeArvl StaArvl TimeTrafficDepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTraffic
330-Dec-22Sun7VO102LHR20:05DEL07:15Inbound30-Dec-22Sun7VO331DEL09:15HYD16:15Outbound
431-Dec-22Sun7VO116FRA10:05DEL22:25Inbound30-Dec-22Sun7VO551DEL08:10MAA11:10Outbound
530-Dec-22Sun7VO124MUC21:45DEL06:50Inbound30-Dec-22Sun7VO125DEL11:50SXR18:50Outbound
631-Dec-22Sun7VO132CDG21:10DEL07:10Inbound1-Jan-23Sun7VO409DEL02:25BOM04:25Outbound
730-Dec-22Sun7VO145DEL08:10CHR18:20Outbound
830-Dec-22Sun7VO155DEL13:00ZRO19:05Outbound
930-Dec-22Sun7VO171DEL11:35ISO19:00Outbound
1031-Dec-22Sun7VO209DEL23:30FRS06:40Outbound
11
12OutPut
13Need 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
18Examples
19DepDOWFrqAlnFLt NoDept StaDept TimeArvl StaArvl TimeTrafficConx TimeDepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTraffic
2030-Dec-22Sun7VO102LHR20:05DEL07:15Inbound02:0030-Dec-22Sun7VO331DEL09:15HYD16:15Outbound
2130-Dec-22Sun7VO102LHR20:05DEL07:15Inbound00:5530-Dec-22Sun7VO551DEL08:10MAA11:10Outbound
2230-Dec-22Sun7VO124MUC21:45DEL06:50Inbound05:0030-Dec-22Sun7VO125DEL11:50SXR18:50Outbound
2330-Dec-22Sun7VO124MUC21:45DEL06:50Inbound01:2030-Dec-22Sun7VO145DEL08:10CHR18:20Outbound
2431-Dec-22Sun7VO132CDG21:10DEL07:10Inbound05:5030-Dec-22Sun7VO155DEL13:00ZRO19:05Outbound
2531-Dec-22Sun7VO132CDG21:10DEL07:10Inbound04:2530-Dec-22Sun7VO171DEL11:35ISO19:00Outbound
2631-Dec-22Sun7VO116FRA10:05DEL22:25Inbound04:001-Jan-23Sun7VO409DEL02:25BOM04:25Outbound
2731-Dec-22Sun7VO116FRA10:05DEL22:25Inbound01:0531-Dec-22Sun7VO209DEL23:30FRS06:40Outbound
28
Sheet3
 
Upvote 0
1672055163350.png

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?
1672055223196.png
 
Last edited by a moderator:
Upvote 0
It's my bad... sorry .. Made changes to data.

Khalid_COnnectivity_Working.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1Data Set 1Data Set 2
2DepDOWFrqAlnFLt NoDept StaDept TimeArvl StaArvl TimeTrafficDepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTraffic
330-Dec-22Sun7VO102LHR20:05DEL07:15Inbound30-Dec-22Sun7VO331DEL09:15HYD16:15Outbound
431-Dec-22Sun7VO116FRA10:05DEL22:25Inbound30-Dec-22Sun7VO551DEL08:10MAA04:10Outbound
530-Dec-22Sun7VO124MUC21:45DEL06:50Inbound30-Dec-22Sun7VO125DEL11:50SXR18:50Outbound
631-Dec-22Sun7VO132CDG21:10DEL07:10Inbound1-Jan-23Sun7VO409DEL14:45CDG19:25Outbound
730-Dec-22Sun7VO145DEL08:10CHR18:20Outbound
831-Dec-22Sun7VO155DEL13:00ZRO19:05Outbound
931-Dec-22Sun7VO171DEL11:35ISO19:00Outbound
1031-Dec-22Sun7VO209DEL23:30FRS06:40Outbound
11
12OutPut
13Need 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
18Examples
19DepDOWFrqAlnFLt NoDept StaDept TimeArvl StaArvl TimeTrafficConx TimeDepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTraffic
2030-Dec-22Sun7VO102LHR20:05DEL07:15Inbound02:0030-Dec-22Sun7VO331DEL09:15HYD16:15Outbound
2130-Dec-22Sun7VO102LHR20:05DEL07:15Inbound00:5530-Dec-22Sun7VO551DEL08:10MAA04:10Outbound
2230-Dec-22Sun7VO124MUC21:45DEL06:50Inbound05:0030-Dec-22Sun7VO125DEL11:50SXR18:50Outbound
2330-Dec-22Sun7VO124MUC21:45DEL06:50Inbound01:2030-Dec-22Sun7VO145DEL08:10CHR18:20Outbound
2431-Dec-22Sun7VO132CDG21:10DEL07:10Inbound05:5031-Dec-22Sun7VO155DEL13:00ZRO19:05Outbound
2531-Dec-22Sun7VO132CDG21:10DEL07:10Inbound04:2531-Dec-22Sun7VO171DEL11:35ISO19:00Outbound
2631-Dec-22Sun7VO116FRA10:05DEL22:25Inbound04:001-Jan-23Sun7VO409DEL02:25BOM04:25Outbound
2731-Dec-22Sun7VO116FRA10:05DEL22:25Inbound01:0531-Dec-22Sun7VO209DEL23:30FRS06:40Outbound
28
Sheet3
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Book1
ABCDEFGHIJKLM
1DepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTrafficDepart DateTimeArrival DateTimeTravel Time
212/30/2022Sun7VO102LHR08:05:00 PMDEL07:15:00 AMInbound12/30/22 8:05 PM12/31/22 7:15 AM0.11:10:00
312/31/2022Sun7VO116FRA10:05:00 AMDEL10:25:00 PMInbound12/31/22 10:05 AM12/31/22 10:25 PM0.12:20:00
412/30/2022Sun7VO124MUC09:45:00 PMDEL06:50:00 AMInbound12/30/22 9:45 PM12/31/22 6:50 AM0.09:05:00
512/31/2022Sun7VO132CDG09:10:00 PMDEL07:10:00 AMInbound12/31/22 9:10 PM1/1/23 7:10 AM0.10:00:00
DS1Flights

Book1
ABCDEFGHIJKLM
1DepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTrafficDepart DateTimeArrival DateTimeTravel Time
212/30/2022Sun7VO331DEL09:15:00 AMHYD04:15:00 PMOutbound12/30/22 9:15 AM12/30/22 4:15 PM0.07:00:00
312/30/2022Sun7VO551DEL08:10:00 AMMAA11:10:00 AMOutbound12/30/22 8:10 AM12/30/22 11:10 AM0.03:00:00
412/30/2022Sun7VO125DEL11:50:00 AMSXR06:50:00 PMOutbound12/30/22 11:50 AM12/30/22 6:50 PM0.07:00:00
501/01/2023Sun7VO409DEL02:25:00 AMBOM04:25:00 AMOutbound1/1/23 2:25 AM1/1/23 4:25 AM0.02:00:00
612/30/2022Sun7VO145DEL08:10:00 AMCHR06:20:00 PMOutbound12/30/22 8:10 AM12/30/22 6:20 PM0.10:10:00
712/30/2022Sun7VO155DEL01:00:00 PMZRO07:05:00 PMOutbound12/30/22 1:00 PM12/30/22 7:05 PM0.06:05:00
812/30/2022Sun7VO171DEL11:35:00 AMISO07:00:00 PMOutbound12/30/22 11:35 AM12/30/22 7:00 PM0.07:25:00
912/31/2022Sun7VO209DEL11:30:00 PMFRS06:40:00 AMOutbound12/31/22 11:30 PM1/1/23 6:40 AM0.07:10:00
DS2Flights

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.
Book1
ABCDEFGHIJKLMNO
1DepDOWFrqAlnFlt NumDept StaDept TimeArvl StaArvl TimeTrafficDepart DateTimeArrival DateTimeTravel TimeNext DS2 FlightConx Flt
212/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 PM209
312/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 AM409
412/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!
DS1Flights
Cell Formulas
RangeFormula
N2N2=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?
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTUVWXY
1Data Set 1Data Set 2
2DepDOWFrqAlnFLt NoDept StaDepart DateTimeArrival DateTimeDept TimeArvl StaArvl TimeTrafficDepDOWFrqAlnFlt NumDept StaDepart DateTimeArrival DateTimeDept TimeArvl StaArvl TimeTraffic
330-Dec-22Sun7VO102LHR12/30/22 14:0512/30/22 22:1514:05DEL22:15Inbound31-Dec-22Sun7VO331DEL12/31/22 4:1512/31/22 8:1504:15HYD08:15Outbound
431-Dec-22Sun7VO116FRA12/31/22 10:0512/31/22 22:2510:05DEL22:25Inbound31-Dec-22Sun7VO551DEL12/31/22 3:1012/31/22 4:1003:10MAA10:10Outbound
530-Dec-22Sun7VO124MUC12/30/22 21:4512/31/22 6:5021:45DEL06:50Inbound31-Dec-22Sun7VO125DEL12/31/22 11:5012/31/22 18:5011:50SXR14:50Outbound
631-Dec-22Sun7VO132CDG12/31/22 21:101/1/23 7:1021:10DEL07:10Inbound1-Jan-23Sun7VO409DEL1/1/23 12:451/1/23 19:2512:45BOM19:25Outbound
730-Dec-22Sun7VO145DEL12/30/22 23:1512/31/22 3:2023:15CHR03:20Outbound
831-Dec-22Sun7VO155DEL12/31/22 12:5012/31/22 19:0512:50ZRO15:05Outbound
931-Dec-22Sun7VO171DEL12/31/22 11:3512/31/22 19:0011:35ISO14:00Outbound
1031-Dec-22Sun7VO209DEL12/31/22 23:301/1/23 6:4023:30FRS06:40Outbound
11
12OutPut
13Need 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
18Examples
19DepDOWFrqAlnFLt NoDept StaDepart DateTimeArrival DateTimeDept TimeArvl StaArvl TimeTrafficConx TimeDepDOWFrqAlnFlt NumDept StaDepart DateTimeArrival DateTimeDept TimeArvl StaArvl TimeTraffic
2030-Dec-22Sun7VO102LHR12/30/22 14:0512/30/22 22:1514:05DEL22:15Inbound06:0031-Dec-22Sun7VO331DEL12/31/22 4:1512/31/22 8:1504:15HYD08:15Outbound
2130-Dec-22Sun7VO102LHR12/30/22 14:0512/30/22 22:1514:05DEL22:15Inbound04:5531-Dec-22Sun7VO551DEL12/31/22 3:1012/31/22 4:1003:10MAA10:10Outbound
2230-Dec-22Sun7VO102LHR12/30/22 14:0512/30/22 22:1514:05DEL22:15Inbound01:0030-Dec-22Sun7VO145DEL12/30/22 23:1512/31/22 3:2023:15CHR03:20Outbound
2331-Dec-22Sun7VO116FRA12/31/22 10:0512/31/22 22:2510:05DEL22:25Inbound01:0531-Dec-22Sun7VO209DEL12/31/22 23:301/1/23 6:4023:30FRS06:40Outbound
2430-Dec-22Sun7VO124MUC12/30/22 21:4512/31/22 6:5021:45DEL06:50Inbound05:0031-Dec-22Sun7VO125DEL12/31/22 11:5012/31/22 18:5011:50SXR14:50Outbound
2530-Dec-22Sun7VO124MUC12/30/22 21:4512/31/22 6:5021:45DEL06:50Inbound04:4531-Dec-22Sun7VO171DEL12/31/22 11:3512/31/22 19:0011:35ISO14:00Outbound
2630-Dec-22Sun7VO124MUC12/30/22 21:4512/31/22 6:5021:45DEL06:50Inbound06:0031-Dec-22Sun7VO155DEL12/31/22 12:5012/31/22 19:0512:50ZRO15:05Outbound
2731-Dec-22Sun7VO132CDG12/31/22 21:101/1/23 7:1021:10DEL07:10Inbound05:351-Jan-23Sun7VO409DEL1/1/23 12:451/1/23 19:2512:45BOM19:25Outbound
28
29
Sheet3
Cell Formulas
RangeFormula
M20:M27M20=T20-H20
 
Upvote 0
You must log in or register to reply here.

Similar threads

skyh3ck
  • Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
322
skyh3ck
skyh3ck
D
  • Question
FILTER function looking at duration and time
Replies
1
Views
250
dancer5
D
S
  • Question
Merge two data sets based on time window
2
Replies
10
Views
322
shansakhi
S
L
  • Solved
Lookup with criteria and give actual sum base on two criteria
2 3
Replies
24
Views
2K
etaf
etaf
L
  • Question
Averageifs with multiple criteria based on KPI target on monthly
Replies
0
Views
695
Lukma
L

Forum statistics

Threads
1,216,096
Messages
6,128,809
Members
449,468
Latest member
AGreen17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top