Macro Help_Urgent

[Shantanu_4612]

Hi,

Need your help with the below sample of data, I am in a great mess, as my employees are fooling me,
My employee, who I have appointed to do all the data work, is saying this is not possible to do.

Please help me with it, I am running in huge losses.

Summary.: Data is Entry and exit time of a particular person in my office.

Challenge.: Data is not sorted in any manner, it gets updated as people swipe in n swipe out.

For 1 month I have a min data of 10 Lakh, and I have to sort the data of last one year.

Sample.:

[TABLE="width: 1298"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Direction[/TD]
[TD]Reason[/TD]
[TD]Condition[/TD]
[TD]Terminal[/TD]
[TD]Description
[/TD]
[TD]Card Number[/TD]
[TD]Employee Number[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]1:59:46[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12
[/TD]
[TD]497148[/TD]
[TD]1055792[/TD]
[TD]PUJA[/TD]
[TD]SHIRSAT[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:05:30[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12
[/TD]
[TD]684488[/TD]
[TD]850390[/TD]
[TD]AKASH[/TD]
[TD]AGRAWAL[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:07:42[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12[/TD]
[TD]1033024[/TD]
[TD]1036210[/TD]
[TD]DEEPIKA[/TD]
[TD]BATHIJA[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:08:29[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12[/TD]
[TD]1033244[/TD]
[TD]1036363[/TD]
[TD]MUKESH[/TD]
[TD]NAIR[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:08:43[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12[/TD]
[TD]1019824[/TD]
[TD]868977[/TD]
[TD]MANISH[/TD]
[TD]BANSI[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:10:53[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12[/TD]
[TD]486017[/TD]
[TD]1053214[/TD]
[TD]ABHIJEET[/TD]
[TD]NAWGEKAR[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:12:19[/TD]
[TD]Exit[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12[/TD]
[TD]486017[/TD]
[TD]1053214[/TD]
[TD]ABHIJEET[/TD]
[TD]NAWGEKAR[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:13:12[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12[/TD]
[TD]1033009[/TD]
[TD]1036195[/TD]
[TD]NAREN[/TD]
[TD]HAKHU[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:13:44[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12[/TD]
[TD]554873[/TD]
[TD]1063570[/TD]
[TD]RAVI[/TD]
[TD]DHUSIA[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:15:12[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12[/TD]
[TD]268906[/TD]
[TD]1270117[/TD]
[TD]PRASAD[/TD]
[TD]KARMALKAR[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:17:38[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12[/TD]
[TD]357204[/TD]
[TD]1267950[/TD]
[TD]POOJA[/TD]
[TD]THADANI[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:19:30[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12[/TD]
[TD]569727[/TD]
[TD]1065083[/TD]
[TD]AJITH[/TD]
[TD]VASU[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:19:50[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12[/TD]
[TD]611300[/TD]
[TD]773828[/TD]
[TD]PRITI[/TD]
[TD]KADAM[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:23:08[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12
[/TD]
[TD]263605[/TD]
[TD]1269069[/TD]
[TD]AMOL[/TD]
[TD]NANDURKAR[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:23:24[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12
[/TD]
[TD]692623[/TD]
[TD]857676[/TD]
[TD]NILESH[/TD]
[TD]PUSATKAR[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:23:51[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12
[/TD]
[TD]531876[/TD]
[TD]1062353[/TD]
[TD]HORMUZD[/TD]
[TD]SUKHIA[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:23:54[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12
[/TD]
[TD]265058[/TD]
[TD]1269341[/TD]
[TD]AMIT[/TD]
[TD]KATE[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:23:56[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12
[/TD]
[TD]254936[/TD]
[TD]1266109[/TD]
[TD]AMEYA[/TD]
[TD]BEDEKAR[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:23:57[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12
[/TD]
[TD]170939[/TD]
[TD]1247915[/TD]
[TD]RAHUL[/TD]
[TD]PARDESHI[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:23:58[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12[/TD]
[TD]264373[/TD]
[TD]1269212[/TD]
[TD]EDWIN[/TD]
[TD]JOSEPH[/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]2:25:25[/TD]
[TD]Entry[/TD]
[TD]Granted[/TD]
[TD]GRANTED[/TD]
[TD]3F_EN4B[/TD]
[TD]S3 3F DOOR NO 9 TS 12[/TD]
[TD]486017[/TD]
[TD]1053214[/TD]
[TD]ABHIJEET[/TD]
[TD]NAWGEKAR[/TD]
[/TR]
</tbody>[/TABLE]

Out Put,

I want two output files,

1, in another Sheet, In this file, I just want, what was the time that particular person entered office for the first time on that date, and when did he leave office premises fro last time, may be to go home.

[TABLE="width: 880"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Employee Number[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Team Leader[/TD]
[TD]Delivery Manager[/TD]
[TD]Process
[/TD]
[TD]First Entry Time[/TD]
[TD]Last Exit Time[/TD]
[TD]Total In Shift Time[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

2, in next sheet, this file has 2 addons,

a.) Talegating.: Tale gating, is when person swiped his card to go out, but did not swipe in to come in, or visa versa.

If this thing has occurred I just want a entry of yes or no.

Eg.: at 1:30 for a person its showing Exit
n in next entry again at 2:30 its showing exit, there is no entry of Entry from 1:30 to 2:30.

b.)
[TABLE="width: 180"]
<colgroup><col width="180"></colgroup><tbody>[TR]
[TD="class: xl65, width: 180"]Total Exit to Entry Diffrence
[/TD]
[/TR]
</tbody>[/TABLE]

Time difference between Exit and Entry, for that particular day, which would give me an understanding, for how long was the person out.

[TABLE="width: 1309"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Direction
[/TD]
[TD]Reason[/TD]
[TD]Condition[/TD]
[TD]Terminal[/TD]
[TD]Description[/TD]
[TD]Card Number[/TD]
[TD]Employee Number
[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Team Leader[/TD]
[TD]Delivery Manager[/TD]
[TD]Process[/TD]
[TD]TaleGating Status[/TD]
[TD]Total Exit to Entry Diffrence[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD]Blank[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Shantanu_4612]

Can you please also explain me the logic that you have used to find In time, out time and Missing swipes, just for my learning

 

[oldbrewer]

I have a spreadsheet laid out as in post 7, and the formulas give the results as in post 7. If your data is in different rows or columns you need to adjust the formulas. NOTE THAT I HAVE DELETED CERTAIN COLUMNS FOR CLARITY - THIS IS PROBABLY WHY