Pull Data from sheet 1 to sheet 2 based on content in sheet 2 using vba

I have a couple of reports I download on a daily basis.

One report is the employees name and badge number.
The other report is employees absenteeism.

The problem I have is the following:-

The 'Employee Absences' report does not show the employees full name.
The 'Employee List' does show the employees full name.

I need the sheet 'Employee Absences' to pull in the employees full name from the 'Employee List' sheet.

I then need to create a pivot from this information so I can keep a track of employees absences. (occurrences and total days).
I have attached a copy of the workbook.
The sheets within the workbook are exactly as the reports download. When you look at the reports you will understand more why I am struggling.

I would copy/paste the downloaded reports directly into the 'Employee Absences' and 'Employee List' sheets daily. Hopefully then run some code to give me the above.

Any help would be greatly appreciated.

If I were in your shoes and this were my job, the first thing I'd do is ask for a bespoke report that churns this data out in a sensible and useable format!
However, the below will potentially do what you need. I've added comments to explain what each part is doing.

Please Login or Register  to view this content.

I've added a button to the Employee Absences sheet in the attached. Click it and it will run the code.

Depending on your machine it'll take a few seconds to run, but it'll still be quicker than doing it manually.

Some whizz will come along and provide some far more efficient code for you, no doubt.

Any questions, feel free to ask.

Hope it helps.

BSB

Thanks BadlySpelledBuoy that works a treat.

just need to get my head around the rest of what i want to pull out of this like trends, occurrences and amount of sick days.

Just noticed there's a slight problem with the code above. When it unmerges the cells it causes some duplicatw rows. This is caused by your original data layout rather than the code itself.
Also the dates are in text format and Excel won't like trying to pivot off of those.

Adding the below just before the last "End With" will resolve both those issues

Please Login or Register  to view this content.

You could add a formula to a 'helper column' that flags the first day of each absence instance to count how many times each person is off.
Obviously count the records to show the number of days they're off. With a little magictrickery you can calculate average days per absence instance or which specific day of the week they're most often off.

I've done similar work to this in the past and found it rather interesting to cross reference the absence data with certain events. Very telling how many people were off "sick" on the Monday after their team lost a big game on the Sunday just before...

Give us a shout if you need more help with it.

BSB

Hello.
I enclose another approach to the subject in which:

- Your workbook with the data must be closed.

- The macro will ask you to select it.

- And at the end you get the complete data plus a dynamic table with focus on the type of absence.

Please Login or Register  to view this content.