+ Reply to Thread
Results 1 to 7 of 7

Consolidating Data into one Pivot Table/Chart

  1. #1
    Registered User
    Join Date
    07-24-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    23

    Consolidating Data into one Pivot Table/Chart

    Hi all,

    I am trying to consolidate various ranges of data into one pivot table. Perhaps the best way of demonstrating my conundrum is attached.. whilst I can get the total $ sales associated with each Salesman consolidated, I don't appear to be able to apply the additional filter of 'City' to the PivotTable.. compare to on the right hand side where I list all the data in a single range, I can sort first by city, then by Salesman. Is this a limitation of consolidating data within a pivot table, or am I doing something wrong here?

    Apologies as I have had limited experience dealing in the wonders of pivot tables. This certainly looks like a great feature but just not sure whether I have approached this wrong, or whether this is a limitation of the consolidation feature (if so, is there any way to get around this?)

    Any help is much appreciated!

    Thanks

    A

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Consolidating Data into one Pivot Table/Chart

    No attachment.

  3. #3
    Registered User
    Join Date
    07-24-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Consolidating Data into one Pivot Table/Chart

    Quote Originally Posted by kersplash View Post
    No attachment.
    aghhhhh apologies, my newbie status is showing through here.. I can't seem to add an attachment.. is there a trick to this? the attachment box just shows up with a white empty box..

  4. #4
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Consolidating Data into one Pivot Table/Chart

    Go Advanced then Manage Attachments then Upload

  5. #5
    Registered User
    Join Date
    07-24-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Consolidating Data into one Pivot Table/Chart

    thanks, that should be it.. to explain what I mean, I am trying to get an output such as that on the right.. using the data from D1:J5, whereas I can only seem to get it in that format where the data is manually consolidated as per M1:O9
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-24-2013
    Location
    Melbourne
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Consolidating Data into one Pivot Table/Chart

    anyone able to assist? Am trying various ways to manipulate the data but with limited success unfortunately

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,929

    Re: Consolidating Data into one Pivot Table/Chart

    I don't see a way to make a pivot table using the data as arranged in columns A:J, however I do see a way of getting formulas to automatically convert that data arrangement into one similar to the arrangement in columns M:O.
    On sheet two of the attached copy of the file:
    1) A column of sales codes is produced using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    2) The columns of salesmen, cities and shares are produced using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The pivot table on sheet two is then produced using the data range in columns M:P
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 06-02-2016, 01:58 PM
  2. Automating Pivot table and pivot chart creation if data table names unknown
    By Vegiepie2016 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-04-2016, 11:52 AM
  3. Consolidating data with a chart or pivot table
    By ehitt in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-24-2013, 11:08 PM
  4. Consolidating Pivot Tables into 1 pivot table - is this possible?
    By levitt in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-20-2013, 04:10 PM
  5. Consolidating data for pivot table
    By floricita in forum Excel General
    Replies: 0
    Last Post: 09-01-2010, 09:03 AM
  6. Consolidating data in one Pivot Table.
    By Kianuz in forum Excel General
    Replies: 2
    Last Post: 10-23-2009, 10:24 AM
  7. [SOLVED] Consolidating multiple external pivot tables into one pivot table
    By Exiled in forum Excel General
    Replies: 1
    Last Post: 08-13-2006, 07:51 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1