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.:

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

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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.

Date	Employee Number	First Name	Last Name	Team Leader	Delivery Manager	Process	First Entry Time	Last Exit Time	Total In Shift Time
				Blank	Blank	Blank

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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.)

Total Exit to Entry Diffrence

<colgroup><col width="180"></colgroup><tbody>
</tbody>

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

Date

Time

Direction

Reason

Condition

Terminal

Description

Card Number

Employee Number

First Name

Last Name

Team Leader

Delivery Manager

Process

TaleGating Status

Total Exit to Entry Diffrence

Blank

Blank

Blank

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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