Creating a table from multiple PivotTables usable for making a report sheet

[maxveller]

Hi Excel Forum, this is my first post.

I'm in deep need of help. I work as a student helper, and my job is to create reports for each of the departments of the school I work for. These reports include showing the changes in Headcounts from quarter to quarter and the distribution of the different professions for each department.

I have created a Pivot Table for each quarter I have data for. Now I need to find a way to "combine" these Pivots in such a way, that I will be able to show the changes that happen from quarter to quarter in each department. I want the row labels to include each sub-department (there can be more sub-departments in quarter 2 than quarter 1 and this has to update automatically) and I want the column labels to state each quarter with the corresponding data down through the columns.

I am currently working in Excel 2010 as this is the version used by my boss and coworkers.

I have been fiddling around with this for so long and I am yet to find a solution to my problem.

Every attempt of helping me will be much appreciated!

- Max

 

[j_unsuitable]

Hey,

generally I think it would be easier to merge your rawdata and create one Pivot table from there instead of merging several Pivot tables.

And to actually help some actual data would be helpful. A mocked up sheet of your rawdata and a sheet with your desired results. That would take away lot of the guessing.

But I am new here too, so maybe somebody else can be more helpful with the Info available.

Julian

 

[maxveller]

Hi again - thanks for the reply!

I'm not sure merging the data will be a suitable solution. I've been working on a dummy-sheet that you can take a look at. It pretty much describes my situation.

Link to file:
http://www.filedropper.com/dummy-file-pivottables

I really need help for this and I really need to find a solution for my problem soon.

- Max

 

[j_unsuitable]

Hey,

it really would be easiest if you merged your raw data and added a column for quarter.
Then you can just create one Pivot table and use filters for the departments, which would allow you to get rid of the "Include" column.

Check out the Sheet "New Rawdata" I added to your file.
http://www.filedropper.com/copyofdummy-file-pivottables

Julian

 

[maxveller]

Hey Julian

Thank you for taking your time to help me out.

I'm not able to thoroughly check your addition atm, but I am wondering what will happen when data is added to the 3rd and 4th quarter. Will your merged data update automatically?

Max

 

[j_unsuitable]

Hey

No, it does not update automatically.
I would just abandon the seperate rawdata sheets completely and add new data into the merged sheet only.
It makes working with your data a lot easier if its all in one place and filters and the Pivottable allow you to only display what you need at that moment.

Julian

 

[maxveller]

I would too if I was to decide however this is how the institution submits its data, and I haven't got the authority to change that, sadly.

If you could merge data from the separate sheets DYNAMICALLY into one Master sheet and then make a pivot report for that data, that would be perfect. I don't know if that is possible though.

- Max

Hey

No, it does not update automatically.
I would just abandon the seperate rawdata sheets completely and add new data into the merged sheet only.
It makes working with your data a lot easier if its all in one place and filters and the Pivottable allow you to only display what you need at that moment.

Julian

 

[j_unsuitable]

Some people are just fun to work for

http://www.filedropper.com/copyofdummy-file-pivottables2

This might be overkill, but I adapted this Macro (Merge cells from all or some worksheets into one Master sheet) to fit your needs.

What it does:
-it deletes the old "Combined" sheet
-it creates a new Worksheet named "Combined"
-adds the headers in A1 to D1
-puts "Quarter" in E1

-then it cycles through all worksheets which are named "Raw data something" and copys the data starting from row 2 into the "Combined" sheet, extracts the quarter from the sheetname and fills it into column E
-when its done with that it turns the data into a table and names it "CombinedD"

When you create a new pivottable the data source needs to be CombinedD

So basically all you need to do when new Raw data is added is push the button in the sheet "Pivot" and refresh your pivottables.

But as I said, this might be a bit overkill.

Let me know if you run into any problems.

Julian

 

[maxveller]

Well this is certainly exciting!

It seems to work like a charm for the dummy sheet that I've created. I however need to adjust it for the project that I'm really working on. Also I need to make 3 similar macros as I have 4 types of rawdata sets for each quarter (headcounts, absence, retirement and "newcomers"). I've never really worked with macros or VBA before but it really seems to get me where I want to go.

Also would it be easier if I manually added the Quarter column to each raw data set? I can do that if that makes things easier. Then the macro doesn't have to make create those.

Btw Julian, I really appreciate the help you provide, it really means a lot, since this problem has bothered me for quite a while, and I need to get this work done. I will try to learn some Macro/VBA 101 to be able to work with this, but I would really like to have you on the sidelines as this is pretty new to me, if that's okay?

But really, thanks for the help man

- Max

 

[j_unsuitable]

Hah no worries. I am def not a pro at this, but its fun to me to trial and error stuff. And I am actually a student helper myself and first got into macros cause I had to do the same jobs over and over again and thought there had to be a way to make my work easier. And I even used the macro I linked to in my last post for one of those jobs.

What adjustments do you need done for the actual project? What are the differences between the mocked up data and the real data?

Are you working with four different files or do you have 16 raw data sheets in one file? And does the rawdata have the same layout in all four cases?
In case one you could just add the macro to all four files
In case two we could just add another new column to the combined data stating the type which you could then use as a filter in your pivot (in this case it would be important to know how the sheet names for each type differentiate) (and again youd have all your data in one place, which makes things easier in the end )

If the layout is different for the four types youd have to adjust the macro accordingly

Concerning the Quarter, what the macro does is take the two last letters of the sheet name and puts them into column E. So as long as the sheets are always named "Raw data Q*" that works fine.
To me what a macro should do is to complete all the repetetive work for me, so no need for you to fill that in manually if the macro can find the information somewhere.

I really like the base macro because it has comments on what each part actually does, so its easy to modify for specific needs even without having a huge knowledge in VBA. I tried to add comments to the stuff I added and modified, but if you have any questions, just ask.

Julian