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

Originally Posted by simarui

man... that is a hideous export.

will it always be a single week, Sun-Sat? so row 1 of the output can always be the same, and row 2 will key off the dates provided?

looks like WINSCR can be ignored, right?

For your Payroll #'s in Sheet2, safe to assume you just put in dummy values but you actually want the values pulled from column D in Sheet1, right?

it looks like there's an extra column in between Tue and Wed on the Raw Data sheet... is that always going to be there?

it looks like there is an extra row in between some of the Person, Name entries... is there any rhyme/reason to when that would be the case?

Hi

Thank you for your quick reply. That was my first thought when I saw how the data exported. In answer to the question:

The week will always be Sunday to Saturday, Winscr can be ignored as this field has no value.

I did use dummy numbers as it was quicker than getting all of the data from the raw data sheet.

In all the previous reports that I have check the additional column seems to occur between Tue and Wed (not sure why....) and finally as for the additional row in between person, Name entries I have gone through an number of files whilst looking at this and I do not seem to be able to find a logic as to how this would work. it basically seems to be a random occurrence for which I have no explanation.

When the report is originally exported it is exported with merged cells etc, the version that is there is with all of the formatting removed as when I have worked with merged cells in the past in formulas and VBA it has caused more problems than solved.

Once again thank you for your help with this.

D

Hi

Thank you for your reply.

I have had a play with this and it seems to be working well for me for the most part. If I want to extend the ranges that it is referencing though how would I do that?

Thanks again.

Hi

Thank you for the quick response.

I am having some issues with the formula populating correctly and I can't figure out why. I have attached a file with some examples so that you can see the problem.

On results 2 tab I have high lighted where one of the issues occurs.

If you have any questions please let me know

Thank you in advance

Dominic

Thank you for that, I had been looking at the data for most of the day and thought that I may be missing something that was obvious.

Thanks again

D