Matching multiple values in multiple columns across multiple sheets, then return values to first sheet

[Reiper79]

Hi Folks,

I've got two sheets - first one showing overspeed details, the second showing load details.

I need to find an accurate way of looking up a driver's name in the load summary sheet and returning it to the related overspeed in the overspeed sheet.

NOTE: The vehicle number showing in the overspeed sheet could be contained in either "Truck", "Trailer A", or "Trailer B" in the load summary sheet.

I've attached samples of the two sheets.

Can someone please help me out?

Thanks very much.

 

[Peter_SSs]

Could we have some small sample data from each sheet made up so that there is at least some correlation of vehicle, driver, date and time, fill in the expected results manually in the overspeed sheet and provide that in the forum with XL2BB so that we can easily copy for testing?
Also please provide the table name for each table.

 

[Reiper79]

Could we have some small sample data from each sheet made up so that there is at least some correlation of vehicle, driver, date and time, fill in the expected results manually in the overspeed sheet and provide that in the forum with XL2BB so that we can easily copy for testing?
Also please provide the table name for each table.

Hopefully this helps.

NOTE: The vehicle numbers on the overspeeds sheet may not be an exact match to those on the Load Summary sheet. The raw data comes from two different systems that don't match, unfortunately (e.g., QP32.354 and 354, respectively). Sorry.

Thanks very much.

Overspeeds Worksheet:

Test Book.xlsx
	A	B	C	D
1	Vehicle	Driver	Date	Time
2	QP2014	RYAN TURNER (DKR)	09/10/2023	8:30:00 AM
3	QP236	RAFAEL MCGEE	09/10/2023	8:15:00 AM
4	QP237	TONY SMITH	09/10/2023	6:18:00 AM
5	QP238		09/10/2023	9:10:00 AM
6	QP32.354		09/10/2023	6:12:00 PM
7	QP32.356		09/10/2023	10:33:00 AM
8	QP236		10/10/2023	8:08:00 AM
9	QP237		10/10/2023	3:39:00 AM
10	QP238		10/10/2023	5:01:00 AM
11	QP32.354		10/10/2023	11:17:00 AM
12	QP32.355		10/10/2023	6:31:00 AM
13	QR1405		10/10/2023	11:04:00 AM
Overspeeds

Load Summary Worksheet:

Test Book.xlsx
	A	B	C	D	E	F	G
1	Date	Start Time	Finish Time	Driver	Truck	TrailerA	TrailerB
2	09/10/2023	12:05:00 AM	12:40:00 PM	GRAHAM LEE	220		RP2221R
3	09/10/2023	3:00:00 AM	3:45:00 PM	TRAVIS CORNEY	221		QP2409R
4	09/10/2023	6:15:00 AM	4:00:00 PM	TYRONE ROGERS	222		QP1617R
5	09/10/2023	6:00:00 AM	6:15:00 PM	DAVID DEAHM	223		QP2215R
6	09/10/2023	4:00:00 AM	5:30:00 PM	SANGRAM (SAM) SINGH	224		QP2412R
7	09/10/2023	6:00:00 AM	5:30:00 PM	JOHN HAYES	225		QP1606
8	09/10/2023	6:00:00 AM	4:35:00 PM	OWEN TONG	227		QP2207
9	09/10/2023	6:00:00 AM	4:20:00 PM	CHRISTIAN RENWICK (RELIANCE)	228		QP2213R
10	09/10/2023	6:00:00 AM	5:00:00 PM	DAIN MCGRATH	229		QP1804R
11	09/10/2023	9:50:00 AM	8:00:00 PM	GURDEEP SINGH (DKR)	230		QP2407R
12	09/10/2023	4:00:00 AM	5:10:00 PM	ANTHONY MARSDEN	232		QP1605
13	09/10/2023	4:00:00 AM	5:35:00 PM	MARTIN THOMPSON (DRIVER HIRE)	233		QP2411R
14	09/10/2023	10:00:00 AM	6:45:00 PM	VICTOR MOORE	353		QP1616R
15	09/10/2023	8:30:00 PM	8:00:00 AM	RONALD FERNANCE	353		QP2210R
16	09/10/2023	10:00:00 AM	9:15:00 PM	KULJINDER (KD) SINGH	354	SUB12A	2218R
17	09/10/2023	6:00:00 AM	8:30:00 PM	MICHAEL BUNGARD	355	QP1212	QP2217R
18	09/10/2023	8:00:00 AM	11:00:00 PM	KRISHNA GOUNDER (DKR)	356		QP2216R
19	09/10/2023	7:30:00 AM	2:30:00 PM	STEVE BUTINA (BUTINA ENT)	671
20	09/10/2023	8:00:00 AM	2:30:00 PM	JASON SPARKSMAN (ZOOMY)	676
21	09/10/2023	7:00:00 AM	4:30:00 PM	JASON MOLLOY (LOUTTITS TPT)	677
22	09/10/2023	6:30:00 AM	1:00:00 PM	BRENDAN HARE (RABBIT)	680
23	09/10/2023	8:00:00 AM	12:30:00 PM	GLEN SAUNDERS (VN TPT)	871
24	09/10/2023	7:30:00 AM	1:20:00 PM	GAVIN PEACE (VN TRANSPORT)	887
25	09/10/2023	5:00:00 AM	7:45:00 AM	GARRY PURSE (GJP TRANSPORT)	1089
26	09/10/2023	6:00:00 AM	2:30:00 PM	JAGROOP SINGH (AUS MALWA TPT)	1278
27	09/10/2023	7:30:00 AM	11:45:00 AM	AMRINDER CHAHAL (KA CHAHAL)	1279
28	09/10/2023	5:30:00 AM	11:10:00 AM	AJIT SINGH ( JAYDEN SAHI)	1301
29	09/10/2023	6:00:00 AM	5:40:00 PM	SUSAN TONG	1404
30	09/10/2023	6:00:00 AM	5:00:00 PM	MUNG PI	1405
31	09/10/2023	7:50:00 AM	6:00:00 PM	FRANK TICHENER (TITCH & CO)	1438
32	09/10/2023	7:00:00 AM	4:20:00 PM	JASHANDEEP SINGH	1440
33	09/10/2023	8:05:00 AM	5:40:00 PM	RAMANDEEP SINGH	1446
34	09/10/2023	7:00:00 AM	7:30:00 PM	SUKHJIT SINGH GILL (SHERGILL)	1460
35	09/10/2023	6:00:00 AM	1:20:00 PM	GURBAJ SINGH (S AULAKH)	1469
36	09/10/2023	5:00:00 AM	8:30:00 AM	JASON STEELE (CJ'S FREIGHT)	1470
37	09/10/2023	7:00:00 AM	1:40:00 PM	KHAI TRAN (K&E TRANSPORT)	1474
38	09/10/2023	9:40:00 AM	4:40:00 PM	MANDEEP SINGH (NANAK)	1622
39	09/10/2023	9:00:00 AM	6:30:00 PM	INDERJIT SINGH (A&K)	1628
40	09/10/2023	7:00:00 AM	3:30:00 PM	KEREI RANGINUI (SINGH)	2402		TL2413
41	09/10/2023	9:00:00 AM	7:15:00 PM	MANDEEP SINGH (JAPJI)	2406		QP2212R
42	09/10/2023	8:15:00 AM	3:30:00 PM	ROLAND WILSON (RAK)	2410		QP2408R
43	09/10/2023	11:00:00 AM	8:00:00 PM	HARMEET SINGH	2418		QP2209
44	09/10/2023	7:00:00 AM	6:00:00 PM	DAVINDER SINGH (JAPJI)	2432		QP2407R
45	09/10/2023	6:00:00 AM	4:00:00 PM	KELVIN ROBERTS	2440		QP2203
46	09/10/2023	9:00:00 AM	6:30:00 PM	DAVINDER UPPAL	2443		QP2404
47	09/10/2023	6:15:00 AM	7:15:00 PM	JIWEI (DAVID) RAN	2444		R44
48	09/10/2023	7:00:00 AM	3:55:00 PM	GASIM AHMED	1010R
49	09/10/2023	5:25:00 AM	4:50:00 PM	SELMIR SALDIC	1403R
50	09/10/2023	6:15:00 AM	6:15:00 PM	GARETH WILLIAMS (DKR)	1406R
51	09/10/2023	5:00:00 AM	4:45:00 PM	BRENDAN JONES (RELIANCE)	1408R
52	09/10/2023	7:05:00 AM	8:05:00 PM	ERIC WHITE	1418R
53	09/10/2023	5:30:00 AM	5:00:00 PM	ELVIS VELIC	1419R
54	09/10/2023	6:05:00 AM	11:50:00 AM	PRITPAL (PAUL) SIDHU (DKR)	1422R
55	09/10/2023	7:10:00 AM	8:00:00 PM	ROBERT DOUGLAS	1423R
56	09/10/2023	4:00:00 AM	3:00:00 PM	STEVE WISE	1424R
57	09/10/2023	4:00:00 AM	2:30:00 PM	NEIL PHILIP	1425R
58	09/10/2023	4:15:00 AM	4:20:00 PM	HENRY KING	1426R
59	09/10/2023	7:00:00 AM	3:15:00 PM	MARK GILBERT	1427R
60	09/10/2023	6:00:00 AM	4:15:00 PM	FRANK O'SULLIVAN	1428R
61	09/10/2023	8:00:00 AM	12:40:00 PM	RYAN TURNER (DKR)	QP2014
62	09/10/2023	4:00:00 AM	6:45:00 PM	MICHAEL STANSBURY	QP235		QP2205
63	09/10/2023	6:00:00 AM	8:15:00 PM	RAFAEL MCGEE	QP236		QP2403
64	09/10/2023	3:30:00 AM	4:40:00 PM	TONY SMITH	QP237		QP2402R
65	09/10/2023	3:30:00 AM	4:30:00 PM	SONYA REED	QP238		QP2412R
66	09/10/2023	5:00:00 AM	4:15:00 PM	RON STEELE	QR1431R
67	09/10/2023	6:00:00 AM	2:55:00 PM	ALAN MCINTOSH	QR1432
Load Summary

 

[Peter_SSs]

Thanks for the XL2BB sample data.

The vehicle numbers on the overspeeds sheet may not be an exact match to those on the Load Summary sheet.

I need to find an accurate way of looking up a driver's name

Hmm, if the vehicle data may not be an accurate match it is something of a tall order to make the driver lookup accurate.

You also did not give the table names as requested but see if you can make anything from this.

• Table on 'Load Summary' is called 'Table1', Table on 'Overspeeds' is called 'Table2'
• I have added a column to Table1
• If the vehicle from Table2 is not found in Table1 then the formula looks for anything after a "." in the vehicle code. For example "QP32.354" is not found so the formula looks for "354".

Reiper.xlsm
	A	B	C	D	E	F	G	H
1	Date	Start Time	Finish Time	Driver	Truck	TrailerA	TrailerB	Veh Nos
2	9/10/2023	12:05:00 AM	12:40:00 PM	GRAHAM LEE	220		RP2221R	|220|RP2221R|
3	9/10/2023	3:00:00 AM	3:45:00 PM	TRAVIS CORNEY	221		QP2409R	|221|QP2409R|
4	9/10/2023	6:15:00 AM	4:00:00 PM	TYRONE ROGERS	222		QP1617R	|222|QP1617R|
5	9/10/2023	6:00:00 AM	6:15:00 PM	DAVID DEAHM	223		QP2215R	|223|QP2215R|
6	9/10/2023	4:00:00 AM	5:30:00 PM	SANGRAM (SAM) SINGH	224		QP2412R	|224|QP2412R|
7	9/10/2023	6:00:00 AM	5:30:00 PM	JOHN HAYES	225		QP1606	|225|QP1606|
8	9/10/2023	6:00:00 AM	4:35:00 PM	OWEN TONG	227		QP2207	|227|QP2207|
9	9/10/2023	6:00:00 AM	4:20:00 PM	CHRISTIAN RENWICK (RELIANCE)	228		QP2213R	|228|QP2213R|
10	9/10/2023	6:00:00 AM	5:00:00 PM	DAIN MCGRATH	229		QP1804R	|229|QP1804R|
11	9/10/2023	9:50:00 AM	8:00:00 PM	GURDEEP SINGH (DKR)	230		QP2407R	|230|QP2407R|
12	9/10/2023	4:00:00 AM	5:10:00 PM	ANTHONY MARSDEN	232		QP1605	|232|QP1605|
13	9/10/2023	4:00:00 AM	5:35:00 PM	MARTIN THOMPSON (DRIVER HIRE)	233		QP2411R	|233|QP2411R|
14	9/10/2023	10:00:00 AM	6:45:00 PM	VICTOR MOORE	353		QP1616R	|353|QP1616R|
15	9/10/2023	8:30:00 PM	8:00:00 AM	RONALD FERNANCE	353		QP2210R	|353|QP2210R|
16	9/10/2023	10:00:00 AM	9:15:00 PM	KULJINDER (KD) SINGH	354	SUB12A	2218R	|354|SUB12A|2218R|
17	9/10/2023	6:00:00 AM	8:30:00 PM	MICHAEL BUNGARD	355	QP1212	QP2217R	|355|QP1212|QP2217R|
18	9/10/2023	8:00:00 AM	11:00:00 PM	KRISHNA GOUNDER (DKR)	356		QP2216R	|356|QP2216R|
19	9/10/2023	7:30:00 AM	2:30:00 PM	STEVE BUTINA (BUTINA ENT)	671			|671|
20	9/10/2023	8:00:00 AM	2:30:00 PM	JASON SPARKSMAN (ZOOMY)	676			|676|
21	9/10/2023	7:00:00 AM	4:30:00 PM	JASON MOLLOY (LOUTTITS TPT)	677			|677|
22	9/10/2023	6:30:00 AM	1:00:00 PM	BRENDAN HARE (RABBIT)	680			|680|
23	9/10/2023	8:00:00 AM	12:30:00 PM	GLEN SAUNDERS (VN TPT)	871			|871|
24	9/10/2023	7:30:00 AM	1:20:00 PM	GAVIN PEACE (VN TRANSPORT)	887			|887|
25	9/10/2023	5:00:00 AM	7:45:00 AM	GARRY PURSE (GJP TRANSPORT)	1089			|1089|
26	9/10/2023	6:00:00 AM	2:30:00 PM	JAGROOP SINGH (AUS MALWA TPT)	1278			|1278|
27	9/10/2023	7:30:00 AM	11:45:00 AM	AMRINDER CHAHAL (KA CHAHAL)	1279			|1279|
28	9/10/2023	5:30:00 AM	11:10:00 AM	AJIT SINGH ( JAYDEN SAHI)	1301			|1301|
29	9/10/2023	6:00:00 AM	5:40:00 PM	SUSAN TONG	1404			|1404|
30	9/10/2023	6:00:00 AM	5:00:00 PM	MUNG PI	1405			|1405|
31	9/10/2023	7:50:00 AM	6:00:00 PM	FRANK TICHENER (TITCH & CO)	1438			|1438|
32	9/10/2023	7:00:00 AM	4:20:00 PM	JASHANDEEP SINGH	1440			|1440|
33	9/10/2023	8:05:00 AM	5:40:00 PM	RAMANDEEP SINGH	1446			|1446|
34	9/10/2023	7:00:00 AM	7:30:00 PM	SUKHJIT SINGH GILL (SHERGILL)	1460			|1460|
35	9/10/2023	6:00:00 AM	1:20:00 PM	GURBAJ SINGH (S AULAKH)	1469			|1469|
36	9/10/2023	5:00:00 AM	8:30:00 AM	JASON STEELE (CJ'S FREIGHT)	1470			|1470|
37	9/10/2023	7:00:00 AM	1:40:00 PM	KHAI TRAN (K&E TRANSPORT)	1474			|1474|
38	9/10/2023	9:40:00 AM	4:40:00 PM	MANDEEP SINGH (NANAK)	1622			|1622|
39	9/10/2023	9:00:00 AM	6:30:00 PM	INDERJIT SINGH (A&K)	1628			|1628|
40	9/10/2023	7:00:00 AM	3:30:00 PM	KEREI RANGINUI (SINGH)	2402		TL2413	|2402|TL2413|
41	9/10/2023	9:00:00 AM	7:15:00 PM	MANDEEP SINGH (JAPJI)	2406		QP2212R	|2406|QP2212R|
42	9/10/2023	8:15:00 AM	3:30:00 PM	ROLAND WILSON (RAK)	2410		QP2408R	|2410|QP2408R|
43	9/10/2023	11:00:00 AM	8:00:00 PM	HARMEET SINGH	2418		QP2209	|2418|QP2209|
44	9/10/2023	7:00:00 AM	6:00:00 PM	DAVINDER SINGH (JAPJI)	2432		QP2407R	|2432|QP2407R|
45	9/10/2023	6:00:00 AM	4:00:00 PM	KELVIN ROBERTS	2440		QP2203	|2440|QP2203|
46	9/10/2023	9:00:00 AM	6:30:00 PM	DAVINDER UPPAL	2443		QP2404	|2443|QP2404|
47	9/10/2023	6:15:00 AM	7:15:00 PM	JIWEI (DAVID) RAN	2444		R44	|2444|R44|
48	9/10/2023	7:00:00 AM	3:55:00 PM	GASIM AHMED	1010R			|1010R|
49	9/10/2023	5:25:00 AM	4:50:00 PM	SELMIR SALDIC	1403R			|1403R|
50	9/10/2023	6:15:00 AM	6:15:00 PM	GARETH WILLIAMS (DKR)	1406R			|1406R|
51	9/10/2023	5:00:00 AM	4:45:00 PM	BRENDAN JONES (RELIANCE)	1408R			|1408R|
52	9/10/2023	7:05:00 AM	8:05:00 PM	ERIC WHITE	1418R			|1418R|
53	9/10/2023	5:30:00 AM	5:00:00 PM	ELVIS VELIC	1419R			|1419R|
54	9/10/2023	6:05:00 AM	11:50:00 AM	PRITPAL (PAUL) SIDHU (DKR)	1422R			|1422R|
55	9/10/2023	7:10:00 AM	8:00:00 PM	ROBERT DOUGLAS	1423R			|1423R|
56	9/10/2023	4:00:00 AM	3:00:00 PM	STEVE WISE	1424R			|1424R|
57	9/10/2023	4:00:00 AM	2:30:00 PM	NEIL PHILIP	1425R			|1425R|
58	9/10/2023	4:15:00 AM	4:20:00 PM	HENRY KING	1426R			|1426R|
59	9/10/2023	7:00:00 AM	3:15:00 PM	MARK GILBERT	1427R			|1427R|
60	9/10/2023	6:00:00 AM	4:15:00 PM	FRANK O'SULLIVAN	1428R			|1428R|
61	9/10/2023	8:00:00 AM	12:40:00 PM	RYAN TURNER (DKR)	QP2014			|QP2014|
62	9/10/2023	4:00:00 AM	6:45:00 PM	MICHAEL STANSBURY	QP235		QP2205	|QP235|QP2205|
63	9/10/2023	6:00:00 AM	8:15:00 PM	RAFAEL MCGEE	QP236		QP2403	|QP236|QP2403|
64	9/10/2023	3:30:00 AM	4:40:00 PM	TONY SMITH	QP237		QP2402R	|QP237|QP2402R|
65	9/10/2023	3:30:00 AM	4:30:00 PM	SONYA REED	QP238		QP2412R	|QP238|QP2412R|
66	9/10/2023	5:00:00 AM	4:15:00 PM	RON STEELE	QR1431R			|QR1431R|
67	9/10/2023	6:00:00 AM	2:55:00 PM	ALAN MCINTOSH	QR1432			|QR1432|
Load Summary

Cell Formulas
Range	Formula
H2:H67	H2	="|"&TEXTJOIN("|",1,Table1[@[Truck]:[TrailerB]])&"|"

Reiper.xlsm
	A	B	C	D
2	QP2014	RYAN TURNER (DKR)	9/10/2023	8:30:00 AM
3	QP236	RAFAEL MCGEE	9/10/2023	8:15:00 AM
4	QP237	TONY SMITH	9/10/2023	6:18:00 AM
5	QP238	SONYA REED	9/10/2023	9:10:00 AM
6	QP32.354	KULJINDER (KD) SINGH	9/10/2023	6:12:00 PM
7	QP32.356	KRISHNA GOUNDER (DKR)	9/10/2023	10:33:00 AM
8	QP236	Unknown	10/10/2023	8:08:00 AM
9	QP237	Unknown	10/10/2023	3:39:00 AM
10	QP238	Unknown	10/10/2023	5:01:00 AM
11	QP32.354	Unknown	10/10/2023	11:17:00 AM
12	QP32.355	Unknown	10/10/2023	6:31:00 AM
13	QR1405	Unknown	10/10/2023	11:04:00 AM
Overspeeds

Cell Formulas
Range	Formula
B2:B13	B2	=LET(tv,[@Vehicle],vno,Table1[Veh Nos],v,XLOOKUP("*|"&tv&"|*",vno,vno,XLOOKUP("*|"&MID(tv,FIND(".",tv&".")+1,9)&"|*",vno,vno,"",2),2),FILTER(Table1[Driver],(vno=v)*(Table1[Date]=[@Date])*(Table1[Start Time]<=[@Time])*(Table1[Finish Time]>=[@Time]),"Unknown"))

 

[Reiper79]

Thanks for the XL2BB sample data.



Hmm, if the vehicle data may not be an accurate match it is something of a tall order to make the driver lookup accurate.

You also did not give the table names as requested but see if you can make anything from this.

• Table on 'Load Summary' is called 'Table1', Table on 'Overspeeds' is called 'Table2'
• I have added a column to Table1
• If the vehicle from Table2 is not found in Table1 then the formula looks for anything after a "." in the vehicle code. For example "QP32.354" is not found so the formula looks for "354".

Reiper.xlsm
	A	B	C	D	E	F	G	H
1	Date	Start Time	Finish Time	Driver	Truck	TrailerA	TrailerB	Veh Nos
2	9/10/2023	12:05:00 AM	12:40:00 PM	GRAHAM LEE	220		RP2221R	|220|RP2221R|
3	9/10/2023	3:00:00 AM	3:45:00 PM	TRAVIS CORNEY	221		QP2409R	|221|QP2409R|
4	9/10/2023	6:15:00 AM	4:00:00 PM	TYRONE ROGERS	222		QP1617R	|222|QP1617R|
5	9/10/2023	6:00:00 AM	6:15:00 PM	DAVID DEAHM	223		QP2215R	|223|QP2215R|
6	9/10/2023	4:00:00 AM	5:30:00 PM	SANGRAM (SAM) SINGH	224		QP2412R	|224|QP2412R|
7	9/10/2023	6:00:00 AM	5:30:00 PM	JOHN HAYES	225		QP1606	|225|QP1606|
8	9/10/2023	6:00:00 AM	4:35:00 PM	OWEN TONG	227		QP2207	|227|QP2207|
9	9/10/2023	6:00:00 AM	4:20:00 PM	CHRISTIAN RENWICK (RELIANCE)	228		QP2213R	|228|QP2213R|
10	9/10/2023	6:00:00 AM	5:00:00 PM	DAIN MCGRATH	229		QP1804R	|229|QP1804R|
11	9/10/2023	9:50:00 AM	8:00:00 PM	GURDEEP SINGH (DKR)	230		QP2407R	|230|QP2407R|
12	9/10/2023	4:00:00 AM	5:10:00 PM	ANTHONY MARSDEN	232		QP1605	|232|QP1605|
13	9/10/2023	4:00:00 AM	5:35:00 PM	MARTIN THOMPSON (DRIVER HIRE)	233		QP2411R	|233|QP2411R|
14	9/10/2023	10:00:00 AM	6:45:00 PM	VICTOR MOORE	353		QP1616R	|353|QP1616R|
15	9/10/2023	8:30:00 PM	8:00:00 AM	RONALD FERNANCE	353		QP2210R	|353|QP2210R|
16	9/10/2023	10:00:00 AM	9:15:00 PM	KULJINDER (KD) SINGH	354	SUB12A	2218R	|354|SUB12A|2218R|
17	9/10/2023	6:00:00 AM	8:30:00 PM	MICHAEL BUNGARD	355	QP1212	QP2217R	|355|QP1212|QP2217R|
18	9/10/2023	8:00:00 AM	11:00:00 PM	KRISHNA GOUNDER (DKR)	356		QP2216R	|356|QP2216R|
19	9/10/2023	7:30:00 AM	2:30:00 PM	STEVE BUTINA (BUTINA ENT)	671			|671|
20	9/10/2023	8:00:00 AM	2:30:00 PM	JASON SPARKSMAN (ZOOMY)	676			|676|
21	9/10/2023	7:00:00 AM	4:30:00 PM	JASON MOLLOY (LOUTTITS TPT)	677			|677|
22	9/10/2023	6:30:00 AM	1:00:00 PM	BRENDAN HARE (RABBIT)	680			|680|
23	9/10/2023	8:00:00 AM	12:30:00 PM	GLEN SAUNDERS (VN TPT)	871			|871|
24	9/10/2023	7:30:00 AM	1:20:00 PM	GAVIN PEACE (VN TRANSPORT)	887			|887|
25	9/10/2023	5:00:00 AM	7:45:00 AM	GARRY PURSE (GJP TRANSPORT)	1089			|1089|
26	9/10/2023	6:00:00 AM	2:30:00 PM	JAGROOP SINGH (AUS MALWA TPT)	1278			|1278|
27	9/10/2023	7:30:00 AM	11:45:00 AM	AMRINDER CHAHAL (KA CHAHAL)	1279			|1279|
28	9/10/2023	5:30:00 AM	11:10:00 AM	AJIT SINGH ( JAYDEN SAHI)	1301			|1301|
29	9/10/2023	6:00:00 AM	5:40:00 PM	SUSAN TONG	1404			|1404|
30	9/10/2023	6:00:00 AM	5:00:00 PM	MUNG PI	1405			|1405|
31	9/10/2023	7:50:00 AM	6:00:00 PM	FRANK TICHENER (TITCH & CO)	1438			|1438|
32	9/10/2023	7:00:00 AM	4:20:00 PM	JASHANDEEP SINGH	1440			|1440|
33	9/10/2023	8:05:00 AM	5:40:00 PM	RAMANDEEP SINGH	1446			|1446|
34	9/10/2023	7:00:00 AM	7:30:00 PM	SUKHJIT SINGH GILL (SHERGILL)	1460			|1460|
35	9/10/2023	6:00:00 AM	1:20:00 PM	GURBAJ SINGH (S AULAKH)	1469			|1469|
36	9/10/2023	5:00:00 AM	8:30:00 AM	JASON STEELE (CJ'S FREIGHT)	1470			|1470|
37	9/10/2023	7:00:00 AM	1:40:00 PM	KHAI TRAN (K&E TRANSPORT)	1474			|1474|
38	9/10/2023	9:40:00 AM	4:40:00 PM	MANDEEP SINGH (NANAK)	1622			|1622|
39	9/10/2023	9:00:00 AM	6:30:00 PM	INDERJIT SINGH (A&K)	1628			|1628|
40	9/10/2023	7:00:00 AM	3:30:00 PM	KEREI RANGINUI (SINGH)	2402		TL2413	|2402|TL2413|
41	9/10/2023	9:00:00 AM	7:15:00 PM	MANDEEP SINGH (JAPJI)	2406		QP2212R	|2406|QP2212R|
42	9/10/2023	8:15:00 AM	3:30:00 PM	ROLAND WILSON (RAK)	2410		QP2408R	|2410|QP2408R|
43	9/10/2023	11:00:00 AM	8:00:00 PM	HARMEET SINGH	2418		QP2209	|2418|QP2209|
44	9/10/2023	7:00:00 AM	6:00:00 PM	DAVINDER SINGH (JAPJI)	2432		QP2407R	|2432|QP2407R|
45	9/10/2023	6:00:00 AM	4:00:00 PM	KELVIN ROBERTS	2440		QP2203	|2440|QP2203|
46	9/10/2023	9:00:00 AM	6:30:00 PM	DAVINDER UPPAL	2443		QP2404	|2443|QP2404|
47	9/10/2023	6:15:00 AM	7:15:00 PM	JIWEI (DAVID) RAN	2444		R44	|2444|R44|
48	9/10/2023	7:00:00 AM	3:55:00 PM	GASIM AHMED	1010R			|1010R|
49	9/10/2023	5:25:00 AM	4:50:00 PM	SELMIR SALDIC	1403R			|1403R|
50	9/10/2023	6:15:00 AM	6:15:00 PM	GARETH WILLIAMS (DKR)	1406R			|1406R|
51	9/10/2023	5:00:00 AM	4:45:00 PM	BRENDAN JONES (RELIANCE)	1408R			|1408R|
52	9/10/2023	7:05:00 AM	8:05:00 PM	ERIC WHITE	1418R			|1418R|
53	9/10/2023	5:30:00 AM	5:00:00 PM	ELVIS VELIC	1419R			|1419R|
54	9/10/2023	6:05:00 AM	11:50:00 AM	PRITPAL (PAUL) SIDHU (DKR)	1422R			|1422R|
55	9/10/2023	7:10:00 AM	8:00:00 PM	ROBERT DOUGLAS	1423R			|1423R|
56	9/10/2023	4:00:00 AM	3:00:00 PM	STEVE WISE	1424R			|1424R|
57	9/10/2023	4:00:00 AM	2:30:00 PM	NEIL PHILIP	1425R			|1425R|
58	9/10/2023	4:15:00 AM	4:20:00 PM	HENRY KING	1426R			|1426R|
59	9/10/2023	7:00:00 AM	3:15:00 PM	MARK GILBERT	1427R			|1427R|
60	9/10/2023	6:00:00 AM	4:15:00 PM	FRANK O'SULLIVAN	1428R			|1428R|
61	9/10/2023	8:00:00 AM	12:40:00 PM	RYAN TURNER (DKR)	QP2014			|QP2014|
62	9/10/2023	4:00:00 AM	6:45:00 PM	MICHAEL STANSBURY	QP235		QP2205	|QP235|QP2205|
63	9/10/2023	6:00:00 AM	8:15:00 PM	RAFAEL MCGEE	QP236		QP2403	|QP236|QP2403|
64	9/10/2023	3:30:00 AM	4:40:00 PM	TONY SMITH	QP237		QP2402R	|QP237|QP2402R|
65	9/10/2023	3:30:00 AM	4:30:00 PM	SONYA REED	QP238		QP2412R	|QP238|QP2412R|
66	9/10/2023	5:00:00 AM	4:15:00 PM	RON STEELE	QR1431R			|QR1431R|
67	9/10/2023	6:00:00 AM	2:55:00 PM	ALAN MCINTOSH	QR1432			|QR1432|
Load Summary

Cell Formulas
Range	Formula
H2:H67	H2	="|"&TEXTJOIN("|",1,Table1[@[Truck]:[TrailerB]])&"|"

Reiper.xlsm
	A	B	C	D
2	QP2014	RYAN TURNER (DKR)	9/10/2023	8:30:00 AM
3	QP236	RAFAEL MCGEE	9/10/2023	8:15:00 AM
4	QP237	TONY SMITH	9/10/2023	6:18:00 AM
5	QP238	SONYA REED	9/10/2023	9:10:00 AM
6	QP32.354	KULJINDER (KD) SINGH	9/10/2023	6:12:00 PM
7	QP32.356	KRISHNA GOUNDER (DKR)	9/10/2023	10:33:00 AM
8	QP236	Unknown	10/10/2023	8:08:00 AM
9	QP237	Unknown	10/10/2023	3:39:00 AM
10	QP238	Unknown	10/10/2023	5:01:00 AM
11	QP32.354	Unknown	10/10/2023	11:17:00 AM
12	QP32.355	Unknown	10/10/2023	6:31:00 AM
13	QR1405	Unknown	10/10/2023	11:04:00 AM
Overspeeds

Cell Formulas
Range	Formula
B2:B13	B2	=LET(tv,[@Vehicle],vno,Table1[Veh Nos],v,XLOOKUP("*|"&tv&"|*",vno,vno,XLOOKUP("*|"&MID(tv,FIND(".",tv&".")+1,9)&"|*",vno,vno,"",2),2),FILTER(Table1[Driver],(vno=v)*(Table1[Date]=[@Date])*(Table1[Start Time]<=[@Time])*(Table1[Finish Time]>=[@Time]),"Unknown"))

Thanks very much Peter.

I'll have a play with it to understand it better and let you know how I go.

Greatly appreciated.

 

[Peter_SSs]

I'll have a play with it to understand it better and let you know how I go.

Cheers. Hope it helps.