Macro Help_Urgent

Shantanu_4612

New Member
Joined
Oct 3, 2016
Messages
27
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.:

DateTimeDirectionReasonConditionTerminalDescription
Card NumberEmployee NumberFirst NameLast Name
2-Oct1:59:46EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 12
4971481055792PUJASHIRSAT
2-Oct2:05:30EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 12
684488850390AKASHAGRAWAL
2-Oct2:07:42EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 1210330241036210DEEPIKABATHIJA
2-Oct2:08:29EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 1210332441036363MUKESHNAIR
2-Oct2:08:43EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 121019824868977MANISHBANSI
2-Oct2:10:53EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 124860171053214ABHIJEETNAWGEKAR
2-Oct2:12:19ExitGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 124860171053214ABHIJEETNAWGEKAR
2-Oct2:13:12EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 1210330091036195NARENHAKHU
2-Oct2:13:44EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 125548731063570RAVIDHUSIA
2-Oct2:15:12EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 122689061270117PRASADKARMALKAR
2-Oct2:17:38EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 123572041267950POOJATHADANI
2-Oct2:19:30EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 125697271065083AJITHVASU
2-Oct2:19:50EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 12611300773828PRITIKADAM
2-Oct2:23:08EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 12
2636051269069AMOLNANDURKAR
2-Oct2:23:24EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 12
692623857676NILESHPUSATKAR
2-Oct2:23:51EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 12
5318761062353HORMUZDSUKHIA
2-Oct2:23:54EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 12
2650581269341AMITKATE
2-Oct2:23:56EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 12
2549361266109AMEYABEDEKAR
2-Oct2:23:57EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 12
1709391247915RAHULPARDESHI
2-Oct2:23:58EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 122643731269212EDWINJOSEPH
2-Oct2:25:25EntryGrantedGRANTED3F_EN4BS3 3F DOOR NO 9 TS 124860171053214ABHIJEETNAWGEKAR

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

DateEmployee NumberFirst NameLast NameTeam LeaderDelivery ManagerProcess
First Entry TimeLast Exit TimeTotal In Shift Time
BlankBlankBlank

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

DateTimeDirection
ReasonConditionTerminalDescriptionCard NumberEmployee Number
First NameLast NameTeam LeaderDelivery ManagerProcessTaleGating StatusTotal Exit to Entry Diffrence


BlankBlankBlank

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

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
DateTimedate_timeDirectionFirst NameCOUNTname_count
02-Oct01:59:4602-Oct-01:59:46EntryPUJA1PUJA1
02-Oct02:05:3002-Oct-02:05:30EntryAKASH1AKASH1
02-Oct04:14:0002-Oct-04:14:00EntryMANISH1MANISH1
02-Oct02:07:4202-Oct-02:07:42EntryDEEPIKA1DEEPIKA1
02-Oct04:10:0002-Oct-04:10:00ExitPUJA2PUJA2
02-Oct04:12:0002-Oct-04:12:00ExitDEEPIKA2DEEPIKA2
02-Oct04:50:0002-Oct-04:50:00ExitMANISH2MANISH2
02-Oct04:46:0002-Oct-04:46:00EntryPUJA3PUJA3
02-Oct04:48:0002-Oct-04:48:00EntryDEEPIKA3DEEPIKA3
02-Oct04:50:0002-Oct-04:50:00EntryMANISH3MANISH3
02-Oct04:51:0002-Oct-04:51:00ExitPUJA4PUJA4
02-Oct04:53:0002-Oct-04:53:00ExitDEEPIKA4DEEPIKA4
02-Oct04:54:0002-Oct-04:54:00ExitMUKESH1MUKESH1
02-Oct04:55:0002-Oct-04:55:00ExitMANISH4MANISH4
EntryExitequalinoutsintimerefouttimerefintimeouttimeworkedhoursISSUE
AKASH10NOmissing exit
DEEPIKA22YESDEEPIKA1DEEPIKA402-Oct-02:07:4202-Oct-04:53:002.76
MANISH22YESMANISH1MANISH402-Oct-04:14:0002-Oct-04:55:000.68
MUKESH01NOmissing entrance
PUJA22YESPUJA1PUJA402-Oct-01:59:4602-Oct-04:51:002.85
by using 2 helper columns on your main table
and a few more on the calculation table
you have hours worked or the issue defined
as you update your main table the lower table will update automatically

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
thank you for your help

but i am sorry i didnt get what you mean,

please simplify

or if possible can you mail me a sample file??
 
Upvote 0
the lower right table gives you either hours worked or which time stamp is missing - if this meets your needs I will post formulas if not tell me what else you need

(scroll to right to see whole table)
 
Upvote 0
DateTimedate_timeDirectionFirst NameCOUNTname_countrow 1
02-Oct01:59:4602-Oct-01:59:46EntryPUJA1PUJA1
02-Oct02:05:3002-Oct-02:05:30EntryAKASH1AKASH1
02-Oct04:14:0002-Oct-04:14:00EntryMANISH1MANISH1
02-Oct02:07:4202-Oct-02:07:42EntryDEEPIKA1DEEPIKA1
02-Oct04:10:0002-Oct-04:10:00ExitPUJA2PUJA2
02-Oct04:12:0002-Oct-04:12:00ExitDEEPIKA2DEEPIKA2
02-Oct04:50:0002-Oct-04:50:00ExitMANISH2MANISH2
02-Oct04:46:0002-Oct-04:46:00EntryPUJA3PUJA3
02-Oct04:48:0002-Oct-04:48:00EntryDEEPIKA3DEEPIKA3
02-Oct04:50:0002-Oct-04:50:00EntryMANISH3MANISH3
02-Oct04:51:0002-Oct-04:51:00ExitPUJA4PUJA4
02-Oct04:53:0002-Oct-04:53:00ExitDEEPIKA4DEEPIKA4
02-Oct04:54:0002-Oct-04:54:00ExitMUKESH1MUKESH1
02-Oct04:55:0002-Oct-04:55:00ExitMANISH4MANISH4
col Arow G
EntryExitequalinoutsintimerefouttimerefintimeouttimeworkedhoursISSUE
row 20AKASH10NOmissing exit
DEEPIKA22YESDEEPIKA1DEEPIKA402-Oct-02:07:4202-Oct-04:53:002.76
MANISH22YESMANISH1MANISH402-Oct-04:14:0002-Oct-04:55:000.68
MUKESH01NOmissing entrance
PUJA22YESPUJA1PUJA402-Oct-01:59:4602-Oct-04:51:002.85
by using 2 helper columns on your main table
and a few more on the calculation table
you have hours worked or the issue defined
as you update your main table the lower table will update automatically
G20=SUMPRODUCT(($E$2:$E$15=$F20)*($D$2:$D$15=G$19))
H20=SUMPRODUCT(($E$2:$E$15=$F20)*($D$2:$D$15=H$19))
I20=IF(G20=H20,"YES","NO")
J20=IF(I20="NO","",F20&1)
K20=IF(I20="NO","",F20&(G20*2))
L20=IF(K20="","",OFFSET($G$1,MATCH(J20,$G$2:$G$15,0),-4))
M20=IF(L20="","",OFFSET($G$1,MATCH(K20,$G$2:$G$15,0),-4))
N20=IF(M20="","",24*(M20-L20))
O20=IF(N20<>"","",IF(G20>H20,"missing exit","missing entrance"))

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
I am not able to get the result as you are getting it,

What is there is Column c (Date_Time)
F (Count)
N y have you given formulas for 20th row??
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top