Sorting export Data

Hi All

I have an export from a system that I need to sort so that it is easier to work with.

I would like to do this through VBA. I have attached a copy of a sample file. Sheet 1 is how the report will export. Sheet 2 is the ideal output of the report.

Also I have a couple of considerations to take in to account and this is where I am getting stuck:

1 - The team size of the team will change depending on which team I have exported the data for. This would mean that the ranges would need to be dynamic. Employee will always appear to the left of a date and above the peoples names.

2 - The Labour Number is not an individual reference instead this links people back to a Department & Area

3 - any field which contains data like "M 8:00" would need to be changed to something such as "MAT/PAT"

That should pretty much cover it all but if you do have any questions please feel free to drop me a line I will be more than happy to help

Thank you in advance for any support that you may be able to give

D

this collates the data, then you can remove the unwanted EMPLOYEE recs, zero work,etc
rename M8:00
and parse the WorkTime into StartTime and EndTime

run: aParseTime

Please Login or Register  to view this content.

Originally Posted by Bernie Deitrick

Your example workbook only has one sheet, so there is no 'ideal' sorting to view

Hi thanks for the quick response I uploaded the attachment before I had saved the changes to the work book .

Here is I have now added the correct work book to this post.

Hope that this helps more than the last one did

Hi jindon

Thanks for that, that is amazing.

Is there a way of setting the output to be on the same sheet every time at all?

Thank you again

D

Add sheet named "Sheet2" if not in the workbook.

Please Login or Register  to view this content.

Hi Again.

That is great and is working Fantastically. I have just noticed that some people have 2 lines of data. This is due to the way the information is exported by the systems (which can not be changed). is there a way of amending the VBA so that it does one of the 2 options below:

1. Only pulls out the pay codes for example "Hol 8:00, .Ho 4:00, Dec 8:00" and so on

2. Populate 2 worksheets with the lines of data. in worksheet one you would have the full day scheduled events in the second sheet you would have the partial/additional scheduled events.

I know that the above is a little confusing so if you have any questions or require a sample of data please let me know

Thank you again for all of the support with this it really is appreciated!

Thanks

D

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.