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

xs4amit

New Member
Joined
May 21, 2018
Messages
34
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 NoDevice TimeEmp NoEmp NameCard NoEvent IDEvent DescriptionController Name
1236405/08/2018 12:43:009820Samuel890653617Exit GrantedIVS/RTY/GGN/DLXXX001
1238605/08/2018 11:56:009860Smith890731617Exit GrantedIVS/RTY/GGN/DLXXX001
1234605/08/2018 11:01:009820Samuel890653616Entry GrantedIVS/RTY/GGN/DLXXX001
1239405/08/2018 10:57:009860Smith890731616Entry GrantedIVS/RTY/GGN/DLXXX001
1230405/08/2018 10:13:009860Smith890731617Exit GrantedIVS/RTY/GGN/DLXXX001
1230605/08/2018 10:04:009820Samuel890653617Exit GrantedIVS/RTY/GGN/DLXXX001
1237805/08/2018 09:59:009860Smith890731616Entry GrantedIVS/RTY/GGN/DLXXX001
1233205/08/2018 09:15:009860Smith890731617Exit GrantedIVS/RTY/GGN/DLXXX001
1232005/08/2018 08:30:009820Samuel890653616Entry GrantedIVS/RTY/GGN/DLXXX001
1239005/08/2018 08:20:009860Smith890731616Entry GrantedIVS/RTY/GGN/DLXXX001

<tbody>
</tbody>


Where i need to find the following details:

DateEmp NoEmp NameFirst Entry GrantedLast Exit GrantedTotal Hours inside officeNumber of times person did Tailgating
05/08/20189860Smith????????
05/08/20189820Samuel????????

<tbody>
</tbody>

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

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
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>
 
Last edited:

xs4amit

New Member
Joined
May 21, 2018
Messages
34
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,732
Messages
5,524,512
Members
409,583
Latest member
RedHelp

This Week's Hot Topics

Top