Good Morning All,
Using Valet parking as the example, I am trying to find out when the valet issued a ticket so that it can be matched to the right period of time that the valet was in the parking lot.
I have two files (both excel files), first is the ticket file. (This normally has 1000+ lines)
File 1
| Person | DateTime for Ticket (yyyy-mm-dd hh:mm) | Unique Code |
| a,smith | 2022-05-04 12:35 | A12 |
| b,jones | 2022-05-05 15:45 | C54 |
| a,smith | 2022-05-05 15:59 | AA59 |
| c,charlie | 2022-05-05 21:21 | Z65 |
| a,smith | 2022-05-06 01:32 | G56 |
File 2 9(This normally has 20000+ lines)
This file shows the entry and exit time of the person from the site. Often many multiples of entry and exit for people, including across a date shift)
| Person | Entry (yyyy-mm-dd hh:mm) | Exit (yyyy-mm-dd hh:mm) |
| a,smith | 2022-05-04 06:35 | 2022-05-04 15:35 |
| b,jones | 2022-05-05 14:44 | 2022-05-05 16:13 |
| a,smith | 2022-05-05 12:13 | 2022-05-05 16:50 |
| b,jones | 2022-05-05 18:12 | 2022-05-05 23:58 |
| c,charlie | 2022-05-05 19:21 | 2022-05-05 22:13 |
| c,charlie | 2022-05-05 23:59 | 2022-05-06 06:15 |
| a,smith | 2022-05-05 23:59 | 2022-06-06 12:15 |
This should eventually give us:
| Person | Entry (yyyy-mm-dd hh:mm) | Exit (yyyy-mm-dd hh:mm) | DateTime for Ticket (yyyy-mm-dd hh:mm) | Unique Code |
| a,smith | 2022-05-04 06:35 | 2022-05-04 15:35 | 2022-05-04 12:35 | A12 |
| b,jones | 2022-05-05 14:44 | 2022-05-05 16:13 | 2022-05-05 14:44 | C54 |
| a,smith | 2022-05-05 12:13 | 2022-05-05 16:50 | 2022-05-05 15:59 | AA59 |
| b,jones | 2022-05-05 18:12 | 2022-05-05 23:58 | | |
| c,charlie | 2022-05-05 19:21 | 2022-05-05 22:13 | 2022-05-05 21:21 | Z65 |
| c,charlie | 2022-05-05 23:59 | 2022-05-06 06:15 | | |
| a,smith | 2022-05-05 23:59 | 2022-06-06 12:15 | 2022-05-06 01:32 | G56 |
I have tried doing summary's but i come up blank. How is this best solved?
Thank you
Bob
