How to extract 1st entry for each employee on each day

[mr_alok]

My excel data has 3 columns - Date, Time, EmployeeNo. Everyday the system captures timestamp when each employee connects to their remote machine. However, there are scenario, when system captures the info more than once (if they login again).

How can I extract the First entry for each employee, on each day

Date Time EmployeeNo
01/04/2020 08:00:09 A
01/04/2020 08:02:19 B
01/04/2020 08:02:57 A
01/04/2020 08:03:17 C
01/04/2020 08:04:23 D
01/04/2020 08:05:09 B
01/04/2020 08:05:12 C
01/04/2020 08:05:58 D
02/04/2020 08:02:30 D
02/04/2020 08:03:00 D
02/04/2020 08:03:18 A
02/04/2020 08:03:22 B
02/04/2020 08:04:56 B
02/04/2020 08:05:48 A
02/04/2020 08:06:46 D
02/04/2020 08:06:54 C

So looking at the data above, for 01/04/2020, Employee A, B, C, D first entry needs to be extracted (colored in Blue). The 3rd record, where employee A tried again, need to be discarded. Same goes for 02/04/2020.

Any suggestion would be good!

 

[pecoflyer]

A Pivot Table will do it in a couple of seconds

 

[mr_alok]

True, makes sense!. I've uploaded the file.

 

[pecoflyer]

Have you seen my answer?

 

[mr_alok]

Have you seen my answer?

Yes, perfect solution! Thanks

 

[Peter Bartholomew]

A formula solution as an alternative.



The text appear as a result of number and conditional formatting
"EXTRACT";;"SKIP"
The table to the right is a dynamic array with formula
= FILTER( TimeClock, MINIFS( Time, EmployeeNo,EmployeeNo, Date,Date)=Time )

Note: The discrepancy for employee D on the first day is caused by 08:04:23 being text and not a number.