Sorting time card Data

Hi All

I am hoping that someone out there will be able to help me with a problem that I am trying to resolve.

I am trying to sort out data from a Time & Attendance system so that it is all nicely grouped together and worked with greater ease. This is proving to be a problem due to the formatting of the report when exported. Changing the report is not an option.

The amount of data that is exported varies from week to week so whatever code is built would need to be dynamic enough to take this in to account

I have attached a workbook with 2 work sheets. The sheet titled raw data is the data in its rawest format. The second sheet Sorted Data is what I am looking to achieve.

A few things to note would be
1. The rest day shift needs to be on the same line for ease of use
2. The number of hours that have been worked need to be populated from the raw data tab rather than calculated as the system this is extracted from applies working rules/deductions of time
3. The basic column is required as this is the total amount of hours worked in a week
4. Ideally, I would like to be able to work with the data in the total rows so that I can do some sense checks

I know that I am asking a lot, any help that can be offered would be greatly appreciated!

Thanking you all in advance

D