Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.


  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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.
    Andrew

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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.
    Andrew

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.

    Attached Files Attached Files

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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.
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •