Find the last date/time with criteria

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows
I'm trying to find a way I can identify the last date and time in a range but with the condition that it does not fall after another date/time.

So range A2:A1000 has a serial number, range B2:B1000 has a date and time stamp - there will be duplicates of the serial number but each time stamp is unique. On another sheet I have the serial numbers in range C2:C1000 and further date/time stamps in range D2:D1000, I have other data in the range E2:1000.

What I need to do is lookup the value in column A of the first sheet, find it in column C of the 2nd sheet, then go through column D of the 2nd sheet, (where the value in A matches), and find the last date/time without going past the original time in column B of the first sheet, then give me the value from column E on the 2nd.

I've tried to explain as best I can, I know it sound really complicate but maybe this will assist;

So this is sheet 1:

Test workbook.xlsm
AB
1Serial NumberSign Date
2CR200002063401/10/2020 01:22
3CR200002063401/10/2020 01:50
4CR200002063501/10/2020 01:23
5CR200002063501/10/2020 01:51
6CR200002063501/10/2020 02:20
7CR200002063501/10/2020 02:49
8CR200002063501/10/2020 03:13
9CR200002063501/10/2020 03:44
10CR200002063501/10/2020 04:10
Sheet1


And this is sheet 2:

Test workbook.xlsm
CDE
1SerialTimestampValue to return
2CR200002063401/10/2020 01:2015
3CR200002063401/10/2020 01:4930
4CR200002063501/10/2020 01:2160
5CR200002063501/10/2020 01:4515
6CR200002063501/10/2020 02:1030
7CR200002063501/10/2020 02:3915
8CR200002063501/10/2020 03:0360
9CR200002063501/10/2020 03:4360
10CR200002063501/10/2020 04:0930
Sheet2


So if I looked up the time stamp in B2 on sheet 1, the value I'd expect to return is 15, which is the value from E2 on sheet 2. If I looked up the time stamp in B6 on sheet 1 I'd expect the return value to be 30, from E6 on the 2nd sheet.

Does my explanation make sense or have I confused the hell out of everyone?
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
is that what you want?
Serial NumberSign DateSerialTimestampValue to returnSerial NumberLastV2R
CR200002063401/10/2020 01:22:59CR200002063401/10/2020 01:20:5915CR200002063401/10/2020 01:5030
CR200002063401/10/2020 01:50:03CR200002063401/10/2020 01:49:0330CR200002063501/10/2020 04:1060
CR200002063501/10/2020 01:23:39CR200002063501/10/2020 01:21:3960
CR200002063501/10/2020 01:51:15CR200002063501/10/2020 01:45:1515
CR200002063501/10/2020 02:20:28CR200002063501/10/2020 02:10:2830
CR200002063501/10/2020 02:49:39CR200002063501/10/2020 02:39:3915
CR200002063501/10/2020 03:13:31CR200002063501/10/2020 03:03:3160
CR200002063501/10/2020 03:44:19CR200002063501/10/2020 03:43:1960
CR200002063501/10/2020 04:10:14CR200002063501/10/2020 04:09:1430
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=INDEX(Sheet2!$E$2:$E$10,AGGREGATE(14,6,(ROW(Sheet2!$D$2:$D$10)-ROW(Sheet2!$D$2)+1)/(Sheet2!$C$2:$C$10=A2)/(Sheet2!$D$2:$D$10<B2),1))
 
Solution

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows
I'll try both suggestions and come back to you!!
 

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

is that what you want?
Serial NumberSign DateSerialTimestampValue to returnSerial NumberLastV2R
CR200002063401/10/2020 01:22:59CR200002063401/10/2020 01:20:5915CR200002063401/10/2020 01:5030
CR200002063401/10/2020 01:50:03CR200002063401/10/2020 01:49:0330CR200002063501/10/2020 04:1060
CR200002063501/10/2020 01:23:39CR200002063501/10/2020 01:21:3960
CR200002063501/10/2020 01:51:15CR200002063501/10/2020 01:45:1515
CR200002063501/10/2020 02:20:28CR200002063501/10/2020 02:10:2830
CR200002063501/10/2020 02:49:39CR200002063501/10/2020 02:39:3915
CR200002063501/10/2020 03:13:31CR200002063501/10/2020 03:03:3160
CR200002063501/10/2020 03:44:19CR200002063501/10/2020 03:43:1960
CR200002063501/10/2020 04:10:14CR200002063501/10/2020 04:09:1430
Sandy666 sorry that doesn't give me what I'd expect...
 

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows
How about
Excel Formula:
=INDEX(Sheet2!$E$2:$E$10,AGGREGATE(14,6,(ROW(Sheet2!$D$2:$D$10)-ROW(Sheet2!$D$2)+1)/(Sheet2!$C$2:$C$10=A2)/(Sheet2!$D$2:$D$10<B2),1))
Fluff thanks - is there anything you can think of that would produce a #NUM error?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
is there anything you can think of that would produce a #NUM error?
There is no date/time in sheet2 less than the date/time in sheet1 for that serial number.
 

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows
so show expected result
Well in your example, serial number 634 with a sign date of 01/10/2020 01:22:59 should produce 15 because 01:20:59 is the last time.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
It does
+Fluff v2.xlsm
ABCDE
1SerialTimestampValue to return
2CR200002063401/10/2020 01:2015
3CR200002063401/10/2020 01:4930
4CR200002063501/10/2020 01:2160
5CR200002063501/10/2020 01:4515
6CR200002063501/10/2020 02:1030
7CR200002063501/10/2020 02:3915
8CR200002063501/10/2020 03:0360
9CR200002063501/10/2020 03:4360
10CR200002063501/10/2020 04:0930
11
Sheet2


+Fluff v2.xlsm
ABC
1Serial NumberSign Date
2CR200002063401/10/2020 01:2215
3CR200002063401/10/2020 01:5030
4CR200002063501/10/2020 01:2360
5CR200002063501/10/2020 01:5115
6CR200002063501/10/2020 02:2030
7CR200002063501/10/2020 02:4915
8CR200002063501/10/2020 03:1360
9CR200002063501/10/2020 03:4460
10CR200002063501/10/2020 04:1030
Sheet1
Cell Formulas
RangeFormula
C2:C10C2=INDEX(Sheet2!$E$2:$E$10,AGGREGATE(14,6,(ROW(Sheet2!$D$2:$D$10)-ROW(Sheet2!$D$2)+1)/(Sheet2!$C$2:$C$10=A2)/(Sheet2!$D$2:$D$10<B2),1))
 

Watch MrExcel Video

Forum statistics

Threads
1,128,153
Messages
5,628,997
Members
416,358
Latest member
grsaltzman

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
Top