Simplify process for combining reports then splitting them up again

[phtevenexcels]

Hi guys, I'm a new member here but I've been using the forums for a while now and they've been a big help so far.

I'm looking for some help with simplifying a training report I run on a regular basis. I first need to combine 3 reports with VLOOKUPs (or INDEX/MATCH) then I need to split them up again with pivot tables by area, and the whole thing is half a day's work. If I could get some help with figuring out how to either combine the initial reports easier, and/or help with an easy way to split up and save the reports it would make life much easier.

I have one headcount report with employees numbers, names, job titles, location etc., then a training report with employee number, name, training date and training course, and finally a report which tells me employee numbers and names of who is a manager. The headcount has duplicate employee numbers because some employees have more than one job, so one row per job. The training report also has duplicate employee numbers as they can complete each course more than once.

I need to VLOOKUP the 'manager/employee' field into my headcount, then copy the headcount another 3 times so I've got 1 headcount per training course.

I use a pivot to split up my training report across 4 sheets for 4 different courses, and sort by employee number then most recent training completion date. I can then VLOOKUP the course completion date into the matching headcount, and I've got an IF statement to state whether the course was completed or not so this shows better in the summary pivot later on.

Once I've got my base data, I colour each headcount and combine the 4 again on one page. I do a pivot with row - location, values - employee number. I drill into the pivot to open up each location's data, then I've got to insert 4 more pivot tables from this new sheet for each course (per location) as the date ranges for the courses all vary so it wouldn't be correct to show them all on one pivot, as they're not comparable. I've then got to tidy up the formatting, save that location, rinse and repeat another 15 times.

I've tried looking at recording macros but I've never really used them before and as the rows of data can be variable every month, I've had issues with ensuring all rows are included in my steps. I thought maybe PowerPivot might be able to help me but again I'm not experienced on this so struggling a bit.

If I could get any pointers at all on how to link/combine/split my data more efficiently, I'd be really grateful!

 

[alansidman]

This sounds like an ideal situation to use Power Query which allows you to merge your data quickly, pivot the data as necessary and export the results back to Excel. Suggest you post some representative files of your work (sanitized for confidential data) to a site like Box.Net or Dropbox.com. We can then direct you to some viable solutions.

 

[phtevenexcels]

Hi Alan,

Thanks very much for your speedy response. I've created a Dropbox link here https://www.dropbox.com/sh/68zu2wap2137bg8/AABI1xaSUWsU0P7MxTbmlve8a?dl=0.

The data is a sanitized sample of what I normally work with, except it's normally around 8000~ rows for the headcount and training report and around 600 rows for the manager report. The headcount and training reports will have duplicate employee numbers & names as some people have more than 1 role, and have completed training more than once.

 

[alansidman]

I'm trying to follow your logic in the first thread, but it would make more sense to me if you explained it in business terms instead of Excel terms. Step by step, explain in business terms what you are trying to accomplish using the three files you have provided. ie. Need to match Employee ID from Manager File to Employee ID in Headcount File, etc. In this manner, I can provide you with a viable solution using PQ and not your manual steps. Be as specific in your step by step as possible.

 

[phtevenexcels]

Hi Alan,

Thanks for helping me out, I really appreciate it.

My end goal is to have a workbook for each service and division. In the workbook it would show all combined data on one sheet, and training completion pivot tables on 3 other sheets in the workbooks as follows:

Course 1 & 2 (same sheet):
row - service, division, sub division (or division, sub division, unit for the divisional reports)
columns - training completed
filters - manager/employee (filtered for managers only)
values - employee number

Course 3:
row - service, division, sub division (or division, sub division, unit for the divisional reports)
columns - training completed
values - employee number

Course 4 (on a different sheet as date range is different to the rest of courses):
row - service, division, sub division (or division, sub division, unit for the divisional reports)
columns - training completed
values - employee number

To get the reports to this point I do the following:
Open the Manager & Headcount reports. In Headcount report, sort by Service, Division, Sub Division. Insert new column A called Manager/Employee and do VLOOKUP from Manager report, using employee number, to pull through which employees are managers (Manager Profile column). I can fill in the blanks with 'employee' instead of manager.

In the training report, I insert a pivot table with row - training course, values - employee number. I drill into each course total to open up a new sheet with just that course on it (name the sheets Course 1, Course 2 etc.). As there can be multiple employee number entries, I sort these sheets by Employee Number then Course Completed Date.

I've had issues with pulling my training records into the headcount report and being able to have functional pivots so the next bit is really long winded:
Go back to my Headcount report, and copy the sheet 3 times to other sheets within the workbook. Colour the tables a different colour per sheet, and name each sheet Course 1, Course 2 etc.
Insert a new column B & C on Course 1 sheet. In column B, do a VLOOKUP from the corresponding Training course sheet to look up the employee number and return the course completion date. Filter for those who have completed the course, and in column C put 'Course 1 Training Completed', then filter on those who haven't completed training and put 'Course 1 Training Not Completed'.
Repeat these steps for the other 3 Course sheets, then copy the contents of the other 3 sheets back under the first sheet in the Headcount report. So I've technically now got 4 headcounts on the one sheet, but column B & C gives me the completion for each person and course, and I previously coloured the headcounts so these would be distinguishable.

Insert a pivot table with row - service, values - employee numbers. Drill into each service total to open up a new sheet, and copy these sheets to new workbooks, so I'll have the original Headcount as well as 5 Service workbooks now. On one Service (say Northern Ireland on mock files provided) it actually has 7 divisions, so I have to do the same and drill down into each of these divisional totals for another 7 workbooks.

On each Service/Division workbook, I have to insert 3 pivot tables as listed at the top of my response. Then the usual formatting before saving and sending out.

Issues I've encountered when trying to simplify the process is that the headcount, training & manager reports can have a variable number of rows each time I run them and I didn't know how to account for this with recorded macros etc., and on the headcount and training sheets the only common value was the employee number but this will have duplicate entries on both sheets, so didn't get very far with PowerPivot.

 

[alansidman]

You have continued to explain in Excel terms what you are doing. I don't want to know that. I want to understand what you are hoping to accomplish in each step. The reason for this is so that I can develop a responsible solution that may not be the way you are doing it. If you are not interested, then say so, and I will leave this for others. I want to understand the business action and reasons.

 

[phtevenexcels]

Jeez I'm sorry for misunderstanding you, clearly I do want help. It's my first time on a forum and I'm trying to be as clear as possible.


I need to link my employee numbers between the Headcount & Manager report, so I can identify who is a manager and who is an employee. This is because training Course 1 and 2 are only to be completed by managers so I need to be able exclude employees from my final summary table for these courses.


With the Training report, it runs with all 4 courses on the one sheet. I need to use the employee number here to match with the employee number in my Headcount report so I can pull through the training completed date, and what course was completed on that date.
As there are lots of multiple employee numbers in the training report, I need to make sure that I'm pulling through the most recent completion date for each course, per each employee. So far I've been splitting up the courses on to separate sheets and copying the headcount an equal number of times to achieve this.
For the employees who have not completed any training, I need to mark them as 'Not Completed' for each course type.


Once the other report info has been combined into the Headcount report, I need to split the reports up by service & division so I can issue them out. To get them ready for issuing, I need a sheet with the base data for that service/division, and then a sheet with a pivot table showing how many employees per service/division have completed or not completed each course type.


I have used 3 different pivot tables as follows. Because of the differences between the courses, I need to show them on separate tables so I can add notes under each to explain what it's showing.


Course 1 and 2 are for any completion date, but for managers only
Course 3 is for any completion date, for all employees/managers
Course 4 only has a date range of the past year (this is already accounted for when I obtain the training report) for all employees/managers