Getting Effective Office Hours with Access Card Swipe Data - (VBA Preferred)

[xs4amit]

I have a large sheet of data where i have swipe details of access card for a given month.
Considering the team size is too big, data may be upto 20000 Rows.

The format of the data is something like this:

Event No	Device Time	Emp No	Emp Name	Card No	Event ID	Event Description	Controller Name
12364	05/08/2018 12:43:00	9820	Samuel	890653	617	Exit Granted	IVS/RTY/GGN/DLXXX001
12386	05/08/2018 11:56:00	9860	Smith	890731	617	Exit Granted	IVS/RTY/GGN/DLXXX001
12346	05/08/2018 11:01:00	9820	Samuel	890653	616	Entry Granted	IVS/RTY/GGN/DLXXX001
12394	05/08/2018 10:57:00	9860	Smith	890731	616	Entry Granted	IVS/RTY/GGN/DLXXX001
12304	05/08/2018 10:13:00	9860	Smith	890731	617	Exit Granted	IVS/RTY/GGN/DLXXX001
12306	05/08/2018 10:04:00	9820	Samuel	890653	617	Exit Granted	IVS/RTY/GGN/DLXXX001
12378	05/08/2018 09:59:00	9860	Smith	890731	616	Entry Granted	IVS/RTY/GGN/DLXXX001
12332	05/08/2018 09:15:00	9860	Smith	890731	617	Exit Granted	IVS/RTY/GGN/DLXXX001
12320	05/08/2018 08:30:00	9820	Samuel	890653	616	Entry Granted	IVS/RTY/GGN/DLXXX001
12390	05/08/2018 08:20:00	9860	Smith	890731	616	Entry Granted	IVS/RTY/GGN/DLXXX001

<tbody>
</tbody>

Where i need to find the following details:

Date	Emp No	Emp Name	First Entry Granted	Last Exit Granted	Total Hours inside office	Number of times person did Tailgating
05/08/2018	9860	Smith	??	??	??	??
05/08/2018	9820	Samuel	??	??	??	??

<tbody>
</tbody>

I am struggling to get the first entry and last exit data. Any help on this will be highly appreciated.

 

[steve the fish]

Copying your table 1 into A1:H11 and the second table into A16:G18 place this into D17:

=MIN(IF($B$2:$B$11>=A17,IF($B$2:$B$11 < A17+1,IF($C$2:$C$11=B17,IF($G$2:$G$11="Entry Granted",$B$2:$B$11)))))<a17+1,if($c$2:$c$11=b17,if($g$2:$g$11="entry granted",$b$2:$b$11)))))


and into E17:

=MAX(IF($B$2:$B$11>=A17,IF($B$2:$B$11 < A17+1,IF($C$2:$C$11=B17,IF($G$2:$G$11="Exit Granted",$B$2:$B$11)))))
<a17+1,if($c$2:$c$11=b17,if($g$2:$g$11="exit granted",$b$2:$b$11)))))

Enter both CTRL-SHIFT-ENTER. </a17+1,if($c$2:$c$11=b17,if($g$2:$g$11="exit></a17+1,if($c$2:$c$11=b17,if($g$2:$g$11="entry>

 

[xs4amit]

Copying your table 1 into A1:H11 and the second table into A16:G18 place this into D17:

=MIN(IF($B$2:$B$11>=A17,IF($B$2:$B$11 < A17+1,IF($C$2:$C$11=B17,IF($G$2:$G$11="Entry Granted",$B$2:$B$11)))))<a17+1,if($c$2:$c$11=b17,if($g$2:$g$11="entry granted",$b$2:$b$11)))))


and into E17:

=MAX(IF($B$2:$B$11>=A17,IF($B$2:$B$11 < A17+1,IF($C$2:$C$11=B17,IF($G$2:$G$11="Exit Granted",$B$2:$B$11)))))
<a17+1,if($c$2:$c$11=b17,if($g$2:$g$11="exit granted",$b$2:$b$11)))))

Enter both CTRL-SHIFT-ENTER. </a17+1,if($c$2:$c$11=b17,if($g$2:$g$11="exit></a17+1,if($c$2:$c$11=b17,if($g$2:$g$11="entry>

Hi Steve,

Thanks that was a brilliant formula. I forgot to tell that i already tried that. But i have a strange challenge.
My team is working in 2 shifts, one from morning 6:00am till 3:00pm. Whereas second team works from 5:30pm till 2:30am (next day).
With this formula, i am able to get accurate data for team 1. But not able to for team 2.
Also my team strength is 400 violent people, so the data is huge.
So ideally working with a formula is a challenge as sheet gets slow. I am in search of a code.
By the way, thanks for your kind help.