Deleting multiple rows, columns, unmerging, pivot table all at once.

[shina67]

Apologises I did not know really what to call this thread.
I have a report that is downloaded from some software that is really meant to be in word format, however i need to extract data from it so need it in excel.
What i want from the report is a list of employees who have done overtime (column AG) with their respective clocking times (Columns with the headers In, Out above them). Nothing else is needed from the report.
The problem I am having as you can see is that various parts are merged (when unmerged gives the merged info multiple times), there are multiple rows and columns with no info in them. This report typically goes on to row 600, but can on further.

Any help is much appreciated.


I want to be able to build a pivot table from the information so i can add additional things to it like the Department the employee works in etc.

Timesheet - Daily Hours (First & Last Swipe).xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1
2	Timesheet - Daily Hours (First & Last Swipe)
3	Thu 26 September 2024														From 23 September 2024 to 25 September 2024
4
5
6	Period End Date : Sun 29/09/2024
7	Name	Badge	Monday				Tuesday				Wednesday				Thursday				Friday				Saturday				Sunday				Basic	Overtime
8			23/09/2024				24/09/2024				25/09/2024				26/09/2024				27/09/2024				28/09/2024				29/09/2024
9			In		Out		In		Out		In		Out		In		Out		In		Out		In		Out		In		Out
10	GEOFFREY NEIL GREENWOOD	00006
11			06:32		15:45		06:26		15:45		06:42		15:45
12			Total :				Total :				Total :				Total :				Total :				Total :				Total :				24:00	0:00
13					8:00				8:00				8:00
14	ROBERT MOUNTAIN	00042
15			06:29		15:52		06:32		17:45		06:29		17:50
16			Total :				Total :				Total :				Total :				Total :				Total :				Total :				24:00	4:00
17					8:00				10:00				10:00
18	MARTIN JOHN WALKER	00200	AUTH				AUTH				AUTH
19			07:00m		15:45m		07:00m		15:45m		07:00m		15:45m
20			Total :				Total :				Total :				Total :				Total :				Total :				Total :				24:00	0:00
21					8:00				8:00				8:00
22	GARY MADDOX	00217
23			06:48		15:48		06:43		15:50		06:48		15:48
24			Total :				Total :				Total :				Total :				Total :				Total :				Total :				24:00	0:00
25					8:00				8:00				8:00
26	PAUL IAN HEWITT	00289
27			04:57		15:46		04:58		16:45		04:57		15:46
28			Total :				Total :				Total :				Total :				Total :				Total :				Total :				24:00	7:00
29					10:00				11:00				10:00
30	NEIL RIDDLE	00295	HOL				HOL
31			07:00m		15:45m		07:00m		15:45m		06:25		15:47
32			Total :				Total :				Total :				Total :				Total :				Total :				Total :				24:00	0:00
33					8:00				8:00				8:00
34	MARIA NOEMI DE FREIT JOAO	00341	HLFHOL
35			04:48		12:31i		05:28		15:51		06:48		15:47
36			Total :				Total :				Total :				Total :				Total :				Total :				Total :				27:00	1:00
37					7:00				9:00				8:00
38	LEON BEMPA OWUSU	00353
39			06:58		18:00		04:56		15:47		04:54		15:47
40			Total :				Total :				Total :				Total :				Total :				Total :				Total :				24:00	6:15
41					10:15				10:00				10:00
42
43	04/0054														1
44
45	Timesheet - Daily Hours (First & Last Swipe)
46	Thu 26 September 2024														From 23 September 2024 to 25 September 2024
47
48
49	Period End Date : Sun 29/09/2024
50	Name	Badge	Monday				Tuesday				Wednesday				Thursday				Friday				Saturday				Sunday				Basic	Overtime
51			23/09/2024				24/09/2024				25/09/2024				26/09/2024				27/09/2024				28/09/2024				29/09/2024
52			In		Out		In		Out		In		Out		In		Out		In		Out		In		Out		In		Out
53	STEFAN WOLOSZYN	00384
54			06:54		15:45m		06:55		15:48		06:53		15:45m
55			Total :				Total :				Total :				Total :				Total :				Total :				Total :				24:00	0:00
56					8:00				8:00				8:00
57	JOHN MARTIN FISHER	00463
58			05:52		14:47		05:56		14:47		05:56		14:46
59			Total :				Total :				Total :				Total :				Total :				Total :				Total :				24:00	0:00
60					8:00				8:00				8:00
61	RICHMOND OFOSU	00487									AUP
62			04:58		14:54		04:58		14:45		08:15m		09:15m
63			Total :				Total :				Total :				Total :				Total :				Total :				Total :				24:00	3:00
64					9:00				9:00				0:00
65	KEITH SMITH	00512	SIKUP				SIKUP
66			07:00m		15:45m		07:00m		15:45m		05:57		18:00
67			Total :				Total :				Total :				Total :				Total :				Total :				Total :				8:00	3:15
68					0:00				0:00				11:15
69	STEVEN IVANKIV	00565
70			06:51		15:47		05:24		15:46		05:53		15:47
71			Total :				Total :				Total :				Total :				Total :				Total :				Total :				24:00	2:00
72					8:00				9:00				9:00
73	ADAM FIRTH	00585	HOL
74			07:00m		15:45m		06:51		15:45		06:30		15:45
75			Total :				Total :				Total :				Total :				Total :				Total :				Total :				32:00	0:00
76					8:00				8:00				8:00
77	JAMES NEWMAN	00628					SIKUP				SIKUP
78			06:54		15:45		07:00m		15:45m		07:00m		15:45m
79			Total :				Total :				Total :				Total :				Total :				Total :				Total :				8:00	0:00
80					8:00				0:00				0:00
81	MARK SCOTT	00638
82			06:48		15:46		05:28		15:46		05:24		15:49
83			Total :				Total :				Total :				Total :				Total :				Total :				Total :				24:00	2:00
84					8:00				9:00				9:00
85
86	04/0054														2
87
88	Timesheet - Daily Hours (First & Last Swipe)
89	Thu 26 September 2024														From 23 September 2024 to 25 September 2024
90
91
92	Period End Date : Sun 29/09/2024
Page1

 

[jkpieterse]

I hope those aren't the actual names in there?
Can you post the (anonymized) data after unmerging the cells? If I paste this in Excel and unmerge, there is no data in the unmerged cells except for the top-left cell.

 

[HighAndWilder]

I pasted the data into a new worksheet and there were not any merged cells.

What represents their overtime, the figure stated in the last column or the difference between their basic hours and the hours that they have worked?
Have a look at a few examples and there are differences.