Capture In and Out Time from a scrambled excel output

[Keerthiv]

Hi,
I have an excel file with all the employees in and out punch time in a very jumbled format. Have attached a sample for your easy reference.

In-Out Time.xls
	B	C	D	E	F	G	H	I	J	K
2	User ID	Name	Punch Time	I/O Type	Device/Source Detail	Location	Punch Mode	Event Status		Special Function
3
4
5	25/10/2019 - Date of Punch
6	AA	PRAMOD KUMAR MAJHEE	00:03:36	IN
7
8	AA	PRAMOD KUMAR MAJHEE	00:05:39	OUT
9
10	BB	PRABAKARAN.N	00:06:18	IN
11
12	BB	PRABAKARAN.N	00:06:42	IN
13
14	CC	PRAMOD KUMAR MAJHEE	00:38:59	IN
15
16	CC	PRAMOD KUMAR MAJHEE	00:41:04	OUT
17
18	CC	PRAMOD KUMAR MAJHEE	00:56:40	IN
19
20	CC	PRAMOD KUMAR MAJHEE	01:03:18	IN
21
22	CC	PRAMOD KUMAR MAJHEE	01:03:23	OUT
23
24	DD	SOUMYA RANJAN MOHANTY	01:32:01	IN
25
26	DD	SOUMYA RANJAN MOHANTY	01:34:24	OUT
27
28	DD	SOUMYA RANJAN MOHANTY	03:07:00	IN
29
30	DD	SOUMYA RANJAN MOHANTY	03:09:49	OUT
31
32	EE	PRAMOD KUMAR MAJHEE	05:14:21	IN
33
34	EE	PRAMOD KUMAR MAJHEE	05:49:11	OUT
35
Sheet1

There could be multiple punch times for one employee. I would like to have 1st In time and last out time captured for each employee in a different sheet. If out time is not available, the cell value against that particular employee will show blank. The values that need to be capture to another sheet is as below

S.No. Date of Punch Employee Name In Time Out Time Device/Source Detail Location Punch Mode Event Status Special Function

 

[Keerthiv]

Please see attached screenshot

 

[Fluff]

No idea why you get that as I can access the link

As I said, it may be that they are having problems with their site at the moment.

 

[etaf]

the link is opening ok and fast for me using chrome browser, firefox & safari on a mac

 

[Keerthiv]

the link is opening ok and fast for me using chrome browser, firefox & safari on a mac

ok... dont know what the issue is... Thank you!!