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]

 

[oldbrewer]

[TABLE="width: 1230"]
<colgroup><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]date_time[/TD]
[TD]Direction[/TD]
[TD]First Name[/TD]
[TD]COUNT[/TD]
[TD]name_count[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]01:59:46[/TD]
[TD="align: right"]02-Oct-01:59:46[/TD]
[TD]Entry[/TD]
[TD]PUJA[/TD]
[TD="align: right"]1[/TD]
[TD]PUJA1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]02:05:30[/TD]
[TD="align: right"]02-Oct-02:05:30[/TD]
[TD]Entry[/TD]
[TD]AKASH[/TD]
[TD="align: right"]1[/TD]
[TD]AKASH1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:14:00[/TD]
[TD="align: right"]02-Oct-04:14:00[/TD]
[TD]Entry[/TD]
[TD]MANISH[/TD]
[TD="align: right"]1[/TD]
[TD]MANISH1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]02:07:42[/TD]
[TD="align: right"]02-Oct-02:07:42[/TD]
[TD]Entry[/TD]
[TD]DEEPIKA[/TD]
[TD="align: right"]1[/TD]
[TD]DEEPIKA1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:10:00[/TD]
[TD="align: right"]02-Oct-04:10:00[/TD]
[TD]Exit[/TD]
[TD]PUJA[/TD]
[TD="align: right"]2[/TD]
[TD]PUJA2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:12:00[/TD]
[TD="align: right"]02-Oct-04:12:00[/TD]
[TD]Exit[/TD]
[TD]DEEPIKA[/TD]
[TD="align: right"]2[/TD]
[TD]DEEPIKA2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:50:00[/TD]
[TD="align: right"]02-Oct-04:50:00[/TD]
[TD]Exit[/TD]
[TD]MANISH[/TD]
[TD="align: right"]2[/TD]
[TD]MANISH2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:46:00[/TD]
[TD="align: right"]02-Oct-04:46:00[/TD]
[TD]Entry[/TD]
[TD]PUJA[/TD]
[TD="align: right"]3[/TD]
[TD]PUJA3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:48:00[/TD]
[TD="align: right"]02-Oct-04:48:00[/TD]
[TD]Entry[/TD]
[TD]DEEPIKA[/TD]
[TD="align: right"]3[/TD]
[TD]DEEPIKA3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:50:00[/TD]
[TD="align: right"]02-Oct-04:50:00[/TD]
[TD]Entry[/TD]
[TD]MANISH[/TD]
[TD="align: right"]3[/TD]
[TD]MANISH3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:51:00[/TD]
[TD="align: right"]02-Oct-04:51:00[/TD]
[TD]Exit[/TD]
[TD]PUJA[/TD]
[TD="align: right"]4[/TD]
[TD]PUJA4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:53:00[/TD]
[TD="align: right"]02-Oct-04:53:00[/TD]
[TD]Exit[/TD]
[TD]DEEPIKA[/TD]
[TD="align: right"]4[/TD]
[TD]DEEPIKA4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:54:00[/TD]
[TD="align: right"]02-Oct-04:54:00[/TD]
[TD]Exit[/TD]
[TD]MUKESH[/TD]
[TD="align: right"]1[/TD]
[TD]MUKESH1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:55:00[/TD]
[TD="align: right"]02-Oct-04:55:00[/TD]
[TD]Exit[/TD]
[TD]MANISH[/TD]
[TD="align: right"]4[/TD]
[TD]MANISH4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Entry[/TD]
[TD]Exit[/TD]
[TD]equalinouts[/TD]
[TD]intimeref[/TD]
[TD]outtimeref[/TD]
[TD]intime[/TD]
[TD]outtime[/TD]
[TD]workedhours[/TD]
[TD]ISSUE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AKASH[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]missing exit[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DEEPIKA[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]YES[/TD]
[TD]DEEPIKA1[/TD]
[TD]DEEPIKA4[/TD]
[TD="align: right"]02-Oct-02:07:42[/TD]
[TD="align: right"]02-Oct-04:53:00[/TD]
[TD="align: right"]2.76[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MANISH[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]YES[/TD]
[TD]MANISH1[/TD]
[TD]MANISH4[/TD]
[TD="align: right"]02-Oct-04:14:00[/TD]
[TD="align: right"]02-Oct-04:55:00[/TD]
[TD="align: right"]0.68[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MUKESH[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]missing entrance[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PUJA[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]YES[/TD]
[TD]PUJA1[/TD]
[TD]PUJA4[/TD]
[TD="align: right"]02-Oct-01:59:46[/TD]
[TD="align: right"]02-Oct-04:51:00[/TD]
[TD="align: right"]2.85[/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]by using 2 helper columns on your main table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]and a few more on the calculation table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]you have hours worked or the issue defined[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]as you update your main table the lower table will update automatically[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Shantanu_4612]

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

 

[oldbrewer]

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)

 

[Shantanu_4612]

Ok,

Can you please share it, I will run it on the file and check

 

[oldbrewer]

[TABLE="width: 1230"]
<colgroup><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]date_time[/TD]
[TD]Direction[/TD]
[TD]First Name[/TD]
[TD]COUNT[/TD]
[TD]name_count[/TD]
[TD][/TD]
[TD][/TD]
[TD]row 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]01:59:46[/TD]
[TD="align: right"]02-Oct-01:59:46[/TD]
[TD]Entry[/TD]
[TD]PUJA[/TD]
[TD="align: right"]1[/TD]
[TD]PUJA1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]02:05:30[/TD]
[TD="align: right"]02-Oct-02:05:30[/TD]
[TD]Entry[/TD]
[TD]AKASH[/TD]
[TD="align: right"]1[/TD]
[TD]AKASH1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:14:00[/TD]
[TD="align: right"]02-Oct-04:14:00[/TD]
[TD]Entry[/TD]
[TD]MANISH[/TD]
[TD="align: right"]1[/TD]
[TD]MANISH1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]02:07:42[/TD]
[TD="align: right"]02-Oct-02:07:42[/TD]
[TD]Entry[/TD]
[TD]DEEPIKA[/TD]
[TD="align: right"]1[/TD]
[TD]DEEPIKA1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:10:00[/TD]
[TD="align: right"]02-Oct-04:10:00[/TD]
[TD]Exit[/TD]
[TD]PUJA[/TD]
[TD="align: right"]2[/TD]
[TD]PUJA2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:12:00[/TD]
[TD="align: right"]02-Oct-04:12:00[/TD]
[TD]Exit[/TD]
[TD]DEEPIKA[/TD]
[TD="align: right"]2[/TD]
[TD]DEEPIKA2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:50:00[/TD]
[TD="align: right"]02-Oct-04:50:00[/TD]
[TD]Exit[/TD]
[TD]MANISH[/TD]
[TD="align: right"]2[/TD]
[TD]MANISH2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:46:00[/TD]
[TD="align: right"]02-Oct-04:46:00[/TD]
[TD]Entry[/TD]
[TD]PUJA[/TD]
[TD="align: right"]3[/TD]
[TD]PUJA3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:48:00[/TD]
[TD="align: right"]02-Oct-04:48:00[/TD]
[TD]Entry[/TD]
[TD]DEEPIKA[/TD]
[TD="align: right"]3[/TD]
[TD]DEEPIKA3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:50:00[/TD]
[TD="align: right"]02-Oct-04:50:00[/TD]
[TD]Entry[/TD]
[TD]MANISH[/TD]
[TD="align: right"]3[/TD]
[TD]MANISH3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:51:00[/TD]
[TD="align: right"]02-Oct-04:51:00[/TD]
[TD]Exit[/TD]
[TD]PUJA[/TD]
[TD="align: right"]4[/TD]
[TD]PUJA4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:53:00[/TD]
[TD="align: right"]02-Oct-04:53:00[/TD]
[TD]Exit[/TD]
[TD]DEEPIKA[/TD]
[TD="align: right"]4[/TD]
[TD]DEEPIKA4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:54:00[/TD]
[TD="align: right"]02-Oct-04:54:00[/TD]
[TD]Exit[/TD]
[TD]MUKESH[/TD]
[TD="align: right"]1[/TD]
[TD]MUKESH1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Oct[/TD]
[TD="align: right"]04:55:00[/TD]
[TD="align: right"]02-Oct-04:55:00[/TD]
[TD]Exit[/TD]
[TD]MANISH[/TD]
[TD="align: right"]4[/TD]
[TD]MANISH4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]col A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]row G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Entry[/TD]
[TD]Exit[/TD]
[TD]equalinouts[/TD]
[TD]intimeref[/TD]
[TD]outtimeref[/TD]
[TD]intime[/TD]
[TD]outtime[/TD]
[TD]workedhours[/TD]
[TD]ISSUE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]row 20[/TD]
[TD]AKASH[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]missing exit[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DEEPIKA[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]YES[/TD]
[TD]DEEPIKA1[/TD]
[TD]DEEPIKA4[/TD]
[TD="align: right"]02-Oct-02:07:42[/TD]
[TD="align: right"]02-Oct-04:53:00[/TD]
[TD="align: right"]2.76[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MANISH[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]YES[/TD]
[TD]MANISH1[/TD]
[TD]MANISH4[/TD]
[TD="align: right"]02-Oct-04:14:00[/TD]
[TD="align: right"]02-Oct-04:55:00[/TD]
[TD="align: right"]0.68[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MUKESH[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD]NO[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]missing entrance[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PUJA[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD]YES[/TD]
[TD]PUJA1[/TD]
[TD]PUJA4[/TD]
[TD="align: right"]02-Oct-01:59:46[/TD]
[TD="align: right"]02-Oct-04:51:00[/TD]
[TD="align: right"]2.85[/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]by using 2 helper columns on your main table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]and a few more on the calculation table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]you have hours worked or the issue defined[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]as you update your main table the lower table will update automatically[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]G20[/TD]
[TD="colspan: 5"]=SUMPRODUCT(($E$2:$E$15=$F20)*($D$2:$D$15=G$19))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]H20[/TD]
[TD="colspan: 5"]=SUMPRODUCT(($E$2:$E$15=$F20)*($D$2:$D$15=H$19))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I20[/TD]
[TD="colspan: 3"]=IF(G20=H20,"YES","NO")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]J20[/TD]
[TD="colspan: 2"]=IF(I20="NO","",F20&1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]K20[/TD]
[TD="colspan: 3"]=IF(I20="NO","",F20&(G20*2))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L20[/TD]
[TD="colspan: 5"]=IF(K20="","",OFFSET($G$1,MATCH(J20,$G$2:$G$15,0),-4))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]M20[/TD]
[TD="colspan: 5"]=IF(L20="","",OFFSET($G$1,MATCH(K20,$G$2:$G$15,0),-4))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]N20[/TD]
[TD="colspan: 3"]=IF(M20="","",24*(M20-L20))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]O20[/TD]
[TD="colspan: 5"]=IF(N20<>"","",IF(G20>H20,"missing exit","missing entrance"))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Shantanu_4612]

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

 

[oldbrewer]

all formulas are dragged down or across

in C2 =A2&B2

 

[oldbrewer]

in F2 =COUNTIF($E$2:E2,E2)

 

[Shantanu_4612]

No Its not getting executed,

I am getting #N/A error in L, M, N & O

Plus calculating time is high