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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Event No[/TD]
[TD]Device Time[/TD]
[TD]Emp No[/TD]
[TD]Emp Name[/TD]
[TD]Card No[/TD]
[TD]Event ID[/TD]
[TD]Event Description[/TD]
[TD]Controller Name[/TD]
[/TR]
[TR]
[TD]12364[/TD]
[TD]05/08/2018 12:43:00[/TD]
[TD]9820[/TD]
[TD]Samuel[/TD]
[TD]890653[/TD]
[TD]617[/TD]
[TD]Exit Granted[/TD]
[TD]IVS/RTY/GGN/DLXXX001[/TD]
[/TR]
[TR]
[TD]12386[/TD]
[TD]05/08/2018 11:56:00[/TD]
[TD]9860[/TD]
[TD]Smith[/TD]
[TD]890731[/TD]
[TD]617[/TD]
[TD]Exit Granted[/TD]
[TD]IVS/RTY/GGN/DLXXX001[/TD]
[/TR]
[TR]
[TD]12346[/TD]
[TD]05/08/2018 11:01:00[/TD]
[TD]9820[/TD]
[TD]Samuel[/TD]
[TD]890653[/TD]
[TD]616[/TD]
[TD]Entry Granted[/TD]
[TD]IVS/RTY/GGN/DLXXX001[/TD]
[/TR]
[TR]
[TD]12394[/TD]
[TD]05/08/2018 10:57:00[/TD]
[TD]9860[/TD]
[TD]Smith[/TD]
[TD]890731[/TD]
[TD]616[/TD]
[TD]Entry Granted[/TD]
[TD]IVS/RTY/GGN/DLXXX001[/TD]
[/TR]
[TR]
[TD]12304[/TD]
[TD]05/08/2018 10:13:00[/TD]
[TD]9860[/TD]
[TD]Smith[/TD]
[TD]890731[/TD]
[TD]617[/TD]
[TD]Exit Granted[/TD]
[TD]IVS/RTY/GGN/DLXXX001[/TD]
[/TR]
[TR]
[TD]12306[/TD]
[TD]05/08/2018 10:04:00[/TD]
[TD]9820[/TD]
[TD]Samuel[/TD]
[TD]890653[/TD]
[TD]617[/TD]
[TD]Exit Granted[/TD]
[TD]IVS/RTY/GGN/DLXXX001[/TD]
[/TR]
[TR]
[TD]12378[/TD]
[TD]05/08/2018 09:59:00[/TD]
[TD]9860[/TD]
[TD]Smith[/TD]
[TD]890731[/TD]
[TD]616[/TD]
[TD]Entry Granted[/TD]
[TD]IVS/RTY/GGN/DLXXX001[/TD]
[/TR]
[TR]
[TD]12332[/TD]
[TD]05/08/2018 09:15:00[/TD]
[TD]9860[/TD]
[TD]Smith[/TD]
[TD]890731[/TD]
[TD]617[/TD]
[TD]Exit Granted[/TD]
[TD]IVS/RTY/GGN/DLXXX001[/TD]
[/TR]
[TR]
[TD]12320[/TD]
[TD]05/08/2018 08:30:00[/TD]
[TD]9820[/TD]
[TD]Samuel[/TD]
[TD]890653[/TD]
[TD]616[/TD]
[TD]Entry Granted[/TD]
[TD]IVS/RTY/GGN/DLXXX001[/TD]
[/TR]
[TR]
[TD]12390[/TD]
[TD]05/08/2018 08:20:00[/TD]
[TD]9860[/TD]
[TD]Smith[/TD]
[TD]890731[/TD]
[TD]616[/TD]
[TD]Entry Granted[/TD]
[TD]IVS/RTY/GGN/DLXXX001[/TD]
[/TR]
</tbody>[/TABLE]


Where i need to find the following details:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Emp No[/TD]
[TD]Emp Name[/TD]
[TD]First Entry Granted[/TD]
[TD]Last Exit Granted[/TD]
[TD]Total Hours inside office[/TD]
[TD]Number of times person did Tailgating [/TD]
[/TR]
[TR]
[TD]05/08/2018[/TD]
[TD]9860[/TD]
[TD]Smith[/TD]
[TD]??[/TD]
[TD]??[/TD]
[TD]??[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD]05/08/2018[/TD]
[TD]9820[/TD]
[TD]Samuel[/TD]
[TD]??[/TD]
[TD]??[/TD]
[TD]??[/TD]
[TD]??[/TD]
[/TR]
</tbody>[/TABLE]

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.