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

S

shina67

Board Regular
Joined
Sep 18, 2014
Messages
141
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
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1
2Timesheet - Daily Hours (First & Last Swipe)
3Thu 26 September 2024From 23 September 2024 to 25 September 2024
4
5
6Period End Date : Sun 29/09/2024
7NameBadgeMondayTuesdayWednesdayThursdayFridaySaturdaySundayBasicOvertime
823/09/202424/09/202425/09/202426/09/202427/09/202428/09/202429/09/2024
9InOutInOutInOutInOutInOutInOutInOut
10GEOFFREY NEIL GREENWOOD00006
1106:3215:4506:2615:4506:4215:45
12Total :Total :Total :Total :Total :Total :Total :24:000:00
138:008:008:00
14ROBERT MOUNTAIN00042
1506:2915:5206:3217:4506:2917:50
16Total :Total :Total :Total :Total :Total :Total :24:004:00
178:0010:0010:00
18MARTIN JOHN WALKER00200AUTHAUTHAUTH
1907:00m15:45m07:00m15:45m07:00m15:45m
20Total :Total :Total :Total :Total :Total :Total :24:000:00
218:008:008:00
22GARY MADDOX00217
2306:4815:4806:4315:5006:4815:48
24Total :Total :Total :Total :Total :Total :Total :24:000:00
258:008:008:00
26PAUL IAN HEWITT00289
2704:5715:4604:5816:4504:5715:46
28Total :Total :Total :Total :Total :Total :Total :24:007:00
2910:0011:0010:00
30NEIL RIDDLE00295HOLHOL
3107:00m15:45m07:00m15:45m06:2515:47
32Total :Total :Total :Total :Total :Total :Total :24:000:00
338:008:008:00
34MARIA NOEMI DE FREIT JOAO00341HLFHOL
3504:4812:31i05:2815:5106:4815:47
36Total :Total :Total :Total :Total :Total :Total :27:001:00
377:009:008:00
38LEON BEMPA OWUSU00353
3906:5818:0004:5615:4704:5415:47
40Total :Total :Total :Total :Total :Total :Total :24:006:15
4110:1510:0010:00
42
4304/00541
44
45Timesheet - Daily Hours (First & Last Swipe)
46Thu 26 September 2024From 23 September 2024 to 25 September 2024
47
48
49Period End Date : Sun 29/09/2024
50NameBadgeMondayTuesdayWednesdayThursdayFridaySaturdaySundayBasicOvertime
5123/09/202424/09/202425/09/202426/09/202427/09/202428/09/202429/09/2024
52InOutInOutInOutInOutInOutInOutInOut
53STEFAN WOLOSZYN00384
5406:5415:45m06:5515:4806:5315:45m
55Total :Total :Total :Total :Total :Total :Total :24:000:00
568:008:008:00
57JOHN MARTIN FISHER00463
5805:5214:4705:5614:4705:5614:46
59Total :Total :Total :Total :Total :Total :Total :24:000:00
608:008:008:00
61RICHMOND OFOSU00487AUP
6204:5814:5404:5814:4508:15m09:15m
63Total :Total :Total :Total :Total :Total :Total :24:003:00
649:009:000:00
65KEITH SMITH00512SIKUPSIKUP
6607:00m15:45m07:00m15:45m05:5718:00
67Total :Total :Total :Total :Total :Total :Total :8:003:15
680:000:0011:15
69STEVEN IVANKIV00565
7006:5115:4705:2415:4605:5315:47
71Total :Total :Total :Total :Total :Total :Total :24:002:00
728:009:009:00
73ADAM FIRTH00585HOL
7407:00m15:45m06:5115:4506:3015:45
75Total :Total :Total :Total :Total :Total :Total :32:000:00
768:008:008:00
77JAMES NEWMAN00628SIKUPSIKUP
7806:5415:4507:00m15:45m07:00m15:45m
79Total :Total :Total :Total :Total :Total :Total :8:000:00
808:000:000:00
81MARK SCOTT00638
8206:4815:4605:2815:4605:2415:49
83Total :Total :Total :Total :Total :Total :Total :24:002:00
848:009:009:00
85
8604/00542
87
88Timesheet - Daily Hours (First & Last Swipe)
89Thu 26 September 2024From 23 September 2024 to 25 September 2024
90
91
92Period End Date : Sun 29/09/2024
Page1
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,877
Messages
6,175,139
Members
452,615
Latest member
bogeys2birdies

Share this page

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