Macro Help_Urgent

Hi Guys,

I am in a big mess, please help,

I have a Magnetic Chip In and Out Card set for my employees,

When they enter their Floor they Swipe in and while Leaving they Swipe out,

I am facing huge loss, as I am not able to calculate the Excel sheet that I get as dump from it,

I have also found my employees not swiping their card, i.e. for example, I have an entry of Employee Entering the Floor at 1:30 PM, than next available entry is on 2:30 again entering the floor, Entry of Exit is missing, this happens when employee uses someones else access to get through the door, we call it as Talegating.

I am looking to calculate the below parameters

On specific day, for Specific Employee,

Entry Time,
Exit Time,
Total Time out of floor,
Total time on Floor
Total Talegating Instance.

Below mentioned Format is all I am Looking for

Date Employee Number First Name Last Name Login Time Log Out Time TaleGating Instance

Please help me with a Macro to get this data calculated,

As I have 20,00,00 entries for one month, and I have to calculate this data for atleast 6 months,
Using formula is killing the excel.

Below mentioned is the sample of the Raw Dump that I get,

Date Time Direction Reason Condition Terminal Description Card Number Employee Number First Name Last Name
2-Oct 1:59:46 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 497148 1055792 PUJA SHIRSAT
2-Oct 2:05:30 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 684488 850390 AKASH AGRAWAL
2-Oct 2:07:42 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 1033024 1036210 DEEPIKA BATHIJA
2-Oct 2:08:29 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 1033244 1036363 MUKESH NAIR
2-Oct 2:08:43 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 1019824 868977 MANISH BANSI
2-Oct 2:10:53 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 486017 1053214 ABHIJEET NAWGEKAR
2-Oct 2:12:19 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 486017 1053214 ABHIJEET NAWGEKAR
2-Oct 2:13:12 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 1033009 1036195 NAREN HAKHU
2-Oct 2:13:44 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 554873 1063570 RAVI DHUSIA
2-Oct 2:15:12 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 268906 1270117 PRASAD KARMALKAR
2-Oct 2:17:38 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 357204 1267950 POOJA THADANI
2-Oct 2:19:30 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 569727 1065083 AJITH VASU
2-Oct 2:19:50 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 611300 773828 PRITI KADAM
2-Oct 2:23:08 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 263605 1269069 AMOL NANDURKAR
2-Oct 2:23:24 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 692623 857676 NILESH PUSATKAR
2-Oct 2:23:51 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 531876 1062353 HORMUZD SUKHIA
2-Oct 2:23:54 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 265058 1269341 AMIT KATE
2-Oct 2:23:56 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 254936 1266109 AMEYA BEDEKAR
2-Oct 2:23:57 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 170939 1247915 RAHUL PARDESHI
2-Oct 2:23:58 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 264373 1269212 EDWIN JOSEPH
2-Oct 2:25:25 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 486017 1053214 ABHIJEET NAWGEKAR
2-Oct 2:25:29 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 742754 910144 SWAPNIL KAMBLE
2-Oct 2:47:00 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 486017 1053214 ABHIJEET NAWGEKAR
2-Oct 2:51:42 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 486017 1053214 ABHIJEET NAWGEKAR
2-Oct 2:58:08 Entry Granted GRANTED 3F_EN3A S3 3F DOOR NO 6 TS11 730819 892318 NISHAN D'SOUZA
2-Oct 3:07:25 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 357204 1267950 POOJA THADANI
2-Oct 3:15:09 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 357204 1267950 POOJA THADANI
2-Oct 3:32:25 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 268906 1270117 PRASAD KARMALKAR
2-Oct 3:35:33 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 531876 1062353 HORMUZD SUKHIA
2-Oct 3:41:56 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 531876 1062353 HORMUZD SUKHIA
2-Oct 3:46:47 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 268906 1270117 PRASAD KARMALKAR

Please use Employee numbers for calculations, as they are unique, names arent.

Sample of RAW Dump,

In Last post it did not get entered Properly

Date Time Direction Reason Condition Terminal Description Card Number Employee Number First Name Last Name
2-Oct 1:59:46 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 497148 1055792 PUJA SHIRSAT
2-Oct 2:05:30 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 684488 850390 AKASH AGRAWAL
2-Oct 2:07:42 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 1033024 1036210 DEEPIKA BATHIJA
2-Oct 2:08:29 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 1033244 1036363 MUKESH NAIR
2-Oct 2:08:43 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 1019824 868977 MANISH BANSI
2-Oct 2:10:53 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 486017 1053214 ABHIJEET NAWGEKAR
2-Oct 2:12:19 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 486017 1053214 ABHIJEET NAWGEKAR
2-Oct 2:13:12 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 1033009 1036195 NAREN HAKHU
2-Oct 2:13:44 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 554873 1063570 RAVI DHUSIA
2-Oct 2:15:12 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 268906 1270117 PRASAD KARMALKAR
2-Oct 2:17:38 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 357204 1267950 POOJA THADANI
2-Oct 2:19:30 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 569727 1065083 AJITH VASU
2-Oct 2:19:50 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 611300 773828 PRITI KADAM
2-Oct 2:23:08 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 263605 1269069 AMOL NANDURKAR
2-Oct 2:23:24 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 692623 857676 NILESH PUSATKAR
2-Oct 2:23:51 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 531876 1062353 HORMUZD SUKHIA
2-Oct 2:23:54 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 265058 1269341 AMIT KATE
2-Oct 2:23:56 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 254936 1266109 AMEYA BEDEKAR
2-Oct 2:23:57 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 170939 1247915 RAHUL PARDESHI
2-Oct 2:23:58 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 264373 1269212 EDWIN JOSEPH
2-Oct 2:25:25 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 486017 1053214 ABHIJEET NAWGEKAR
2-Oct 2:25:29 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 742754 910144 SWAPNIL KAMBLE
2-Oct 2:47:00 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 486017 1053214 ABHIJEET NAWGEKAR
2-Oct 2:51:42 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 486017 1053214 ABHIJEET NAWGEKAR
2-Oct 2:58:08 Entry Granted GRANTED 3F_EN3A S3 3F DOOR NO 6 TS11 730819 892318 NISHAN D'SOUZA
2-Oct 3:07:25 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 357204 1267950 POOJA THADANI
2-Oct 3:15:09 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 357204 1267950 POOJA THADANI
2-Oct 3:32:25 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 268906 1270117 PRASAD KARMALKAR
2-Oct 3:35:33 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 531876 1062353 HORMUZD SUKHIA
2-Oct 3:41:56 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 531876 1062353 HORMUZD SUKHIA
2-Oct 3:46:47 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 268906 1270117 PRASAD KARMALKAR
2-Oct 3:49:30 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 265058 1269341 AMIT KATE
2-Oct 3:49:35 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 263605 1269069 AMOL NANDURKAR
2-Oct 3:55:15 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 263605 1269069 AMOL NANDURKAR
2-Oct 4:01:42 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 265058 1269341 AMIT KATE
2-Oct 4:17:16 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 264420 1269238 AAKASH RAKHYANI
2-Oct 4:57:17 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 254936 1266109 AMEYA BEDEKAR
2-Oct 4:57:53 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 1033244 1036363 MUKESH NAIR
2-Oct 4:58:21 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 684488 850390 AKASH AGRAWAL
2-Oct 5:00:18 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 254936 1266109 AMEYA BEDEKAR
2-Oct 5:15:35 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 684488 850390 AKASH AGRAWAL
2-Oct 5:15:38 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 1033244 1036363 MUKESH NAIR
2-Oct 5:17:19 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 684488 850390 AKASH AGRAWAL
2-Oct 5:21:22 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 531876 1062353 HORMUZD SUKHIA
2-Oct 5:24:42 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 684488 850390 AKASH AGRAWAL
2-Oct 5:36:13 Entry Granted GRANTED 3F_EN3A S3 3F DOOR NO 6 TS11 385003 1076863 SUMITH NAIR
2-Oct 5:38:02 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 263605 1269069 AMOL NANDURKAR
2-Oct 5:40:26 Entry Granted GRANTED 3F_EN3A S3 3F DOOR NO 6 TS11 569729 1065085 PREETAM SURYAWANSHI
2-Oct 5:40:30 Exit Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 170939 1247915 RAHUL PARDESHI
2-Oct 5:42:01 Entry Granted GRANTED 3F_EN4B S3 3F DOOR NO 9 TS 12 531876 1062353 HORMUZD SUKHIA