Merge two data sets based on time window

[shansakhi]

Hello All,
I have two data sets - Data Set 1 and Data Set 2.
I want to take Bus No from Data set 1 and incorporate in Data Set 2 using below criteria.

1. Dep Date
2. Dept Sta
3. Arvl Sta
4. Need to see if Dept Time in Data Set 1 is coming between DepartureTimeWindow in Data Set 2.

Attached is the Screenshot with Data Set 1, Data Set 2 and Output.

Regards,
Shan

 

[bebo021999]

Output exactly likes Data Set 2. Try again with other sample.

 

[shansakhi]

My apology..
Data Set 2 don't have Bus No column

 

[shansakhi]

Updated screenshot

 

[bebo021999]

We need a sample worksheet to test.
try to attach mini sheet via XL2BB

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[shansakhi]

Sorry, but I do not have authority to down load add in.
I tried searching XL2BB add in in add in list but it is not available.
Is there another way to upload excel file.

 

[bebo021999]

Or try via dropbox or google drive?

 

[shansakhi]

I am able to get the Xl2bb.
Please find below the link. Let me know if I am able to provide the link properly.

Book1
	A	B	C	D	E
1	Date Set 1
2	Dep Date	Bus No	Dept Sta	Arvl Sta	Dept Time
3	28-Nov-22	10222	MAN	ZRH	14:20
4	5-Dec-22	10232	DEL	BOM	1:10
5	15-Dec-22	10023	LHR	MAN	19:05
6
7	Date Set 2
8	Dept Sta	Arvl Sta	DEPARTURE TIME WINDOW	Dep Date
9	MAN	ZRH	1201-1500	20221128
10	MAN	ZRH	1501-2300	20221128
11	DEL	BOM	0000-1800	20221205
12	DEL	BOM	1801-2300	20221205
13	LHR	MAN	1200-2000	20221215
14	LHR	MAN	1200-2000	20221217
15
16	Output
17	Dept Sta	Arvl Sta	DEPARTURE TIME WINDOW	Dep Date	Bus No
18	MAN	ZRH	1201-1500	20221128	10222
19	MAN	ZRH	1501-2300	20221128	0
20	DEL	BOM	0000-1800	20221205	10232
21	DEL	BOM	1801-2300	20221205	0
22	LHR	MAN	1200-2000	20221215	10023
23	LHR	MAN	1200-2000	20221217	0
Sheet1

 

[shansakhi]

Hello Team,
Can anybody assist me for this.

 

[bebo021999]

Is it?

Book1
	A	B	C	D	E
1	Date Set 1
2	Dep Date	Bus No	Dept Sta	Arvl Sta	Dept Time
3	28/11/2022	10222	MAN	ZRH	14:20:00
4	05/12/2022	10232	DEL	BOM	01:10:00
5	15/12/2022	10023	LHR	MAN	19:05:00
6
7	Date Set 2
8	Dept Sta	Arvl Sta	DEPARTURE TIME WINDOW	Dep Date
9	MAN	ZRH	1201-1500	20221128
10	MAN	ZRH	1501-2300	20221128
11	DEL	BOM	0000-1800	20221205
12	DEL	BOM	1801-2300	20221205
13	LHR	MAN	1200-2000	20221215
14	LHR	MAN	1200-2000	20221217
15
16	Output
17	Dept Sta	Arvl Sta	DEPARTURE TIME WINDOW	Dep Date	Bus No
18	MAN	ZRH	1201-1500	20221128	10222
19	MAN	ZRH	1501-2300	20221128
20	DEL	BOM	0000-1800	20221205	10232
21	DEL	BOM	1801-2300	20221205
22	LHR	MAN	1200-2000	20221215	10023
23	LHR	MAN	1200-2000	20221217	10023
Sheet1

Cell Formulas
Range	Formula
E18:E23	E18	=IFERROR(LOOKUP(2,1/($C$3:$C$5=A18)/($D$3:$D$5=B18)/($A$3:$A$5-DATE(LEFT(D18,4)+0,MID(D18,5,2)+0,RIGHT(D18,2)=0))/($E$3:$E$5-(LEFT(C18,2) & ":" & MID(C18,3,2))>=0)/($E$3:$E$5-(MID(C18,6,2)&":"&RIGHT(C18,2))<=0),$B$3:$B$5),"")