PIVOT TABLE WITH MULTIPLE FIELDS – revised

Topic

New Reply

I am working up a pivot table. Sample is attached. From the initial data tab I would like to get to the pivot table tab without manipulating the data as on the realigned data tab.

My real worksheet is quite large. Is there any way of getting this pivot task done without manipulating data?

Sorry about the first posting with no text describing the task at hand.

Reply | Quote

Replies

MNN,

I tried using this range: ‘INITIAL DATA’!$C$1:$I$15,’INITIAL DATA’!$K$1:$Q$15,’INITIAL DATA’!$S$1:$Y$15,’INITIAL DATA’!$AA$1:$AG$15 but Excel came back with a “Reference Not Valid” message.

You might want to reconfigure your “Initial Data sheet” to have multiple lines per employee# {one for each facility he/she works at} then creating the pivot would be no problem. This realignment would also make it easier to verify that you didn’t have more/less than 100% allocation for an employee as the percentages would all be right there together w/o scrolling. This is what you have done with the “Realigned Data” tab except you didn’t sort it by Employee# & Facility. Just a thought.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I appreciate your “look see”. I am trying to get the pivot table done without any realignment of the initial data, since the file is large and would take a lot of time as well as being very cumbersome or writing a macro since I am not versed in MACRO developing.

Thank you.

Reply | Quote

With the data layout as it stands I do not think you are going to be able to achieve what you are after
IF you could get the data onto Separate Tabs with Common Headings and EmpID and GrossPay with each
you could use Multiple Consolidation ranges, BUT Pivot tables do not like Split Range References.
The alternative is I am afraid a Macro
How many different faculty sections are there, and is it possible top request the data in an alternative layout.
It looks like it is coming from a database so it ought to be possible.

Reply | Quote

What is your thoughts on Multiple Consolidation ranges in conjunction with Pivot Tables? I have never used this before but may be a workaround for split range references. How would this work within a context of obtaining a pivot table to summarize the data?

Thanks.

Reply | Quote

Looking at your data a bit more, I do not think it would work, because you are not summarising against the Column Headings, BUT the entries in the rows.
Unfortunately it looks like Macro, or request an alternative layout.

Reply | Quote

MNN,

Sorry, I just can’t resist a chance to write some VBA.

Attached is a copy of your worksheet (including macro).

I had the macro create a sheet called Realigned-Data to be different from yours.
I also noticed that the macro had 1 more row than your REALIGNED DATA sheet.
I counted and it looks like the macro got it right-always a problem with manual manipulations.

Enjoy,

Rg

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I will look at the MACRO that RG wrote a little later. My question is primarily geared to get a pivot table scenario activated since it is common among our staff.

If I develope a workbook where the four (4) sections have their own work sheet, is it possible to do one pivot table consolidating all the 4 worksheets. I am attaching another workbook COMPILATION_TEST-separate worksheets which includes the inital w/s and what the pivot table should look like and 4 worksheets for each group. How would you combine through the PIVOT TABLE PROCEDURE.

Thank you.

Reply | Quote

As I said earlier, with the data on multiple sheets you will not get what you want.
Excel only lets you use Multiple Consolidation ranges against this data,
and that will not create the Pivot Table you want.
Unfortunately you need to get the data into a single List

Even with that scenario (based upon your data) you need to be careful with the
Gross Pay against the Employee ID (If you wanted to use it), because it looks like these should only be included once,
but in a list will be added multiple times

Looks like the Macro solution to me unless someone else can come up with an alternative.

Reply | Quote