sharky12345
Well-known Member
- Joined
- Aug 5, 2010
- Messages
- 3,404
- Office Version
- 2016
- Platform
- 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:
And this is sheet 2:
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?
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 | ||||
---|---|---|---|---|
A | B | |||
1 | Serial Number | Sign Date | ||
2 | CR2000020634 | 01/10/2020 01:22 | ||
3 | CR2000020634 | 01/10/2020 01:50 | ||
4 | CR2000020635 | 01/10/2020 01:23 | ||
5 | CR2000020635 | 01/10/2020 01:51 | ||
6 | CR2000020635 | 01/10/2020 02:20 | ||
7 | CR2000020635 | 01/10/2020 02:49 | ||
8 | CR2000020635 | 01/10/2020 03:13 | ||
9 | CR2000020635 | 01/10/2020 03:44 | ||
10 | CR2000020635 | 01/10/2020 04:10 | ||
Sheet1 |
And this is sheet 2:
Test workbook.xlsm | |||||
---|---|---|---|---|---|
C | D | E | |||
1 | Serial | Timestamp | Value to return | ||
2 | CR2000020634 | 01/10/2020 01:20 | 15 | ||
3 | CR2000020634 | 01/10/2020 01:49 | 30 | ||
4 | CR2000020635 | 01/10/2020 01:21 | 60 | ||
5 | CR2000020635 | 01/10/2020 01:45 | 15 | ||
6 | CR2000020635 | 01/10/2020 02:10 | 30 | ||
7 | CR2000020635 | 01/10/2020 02:39 | 15 | ||
8 | CR2000020635 | 01/10/2020 03:03 | 60 | ||
9 | CR2000020635 | 01/10/2020 03:43 | 60 | ||
10 | CR2000020635 | 01/10/2020 04:09 | 30 | ||
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?