+ Reply to Thread
Results 1 to 4 of 4

Pivot Table - consolidating ranges (again)

  1. #1
    Registered User
    Join Date
    09-16-2005
    Posts
    2

    Pivot Table - consolidating ranges (again)

    This is my first post here...I hope that i've chosen the right category....
    My problem is similiar to other people's when it comes to consolidating ranges with Pivot Table (sorry for my english...). I've searched the forum but I haven't found either solution or workaround for my problem.

    My data is divided into 2 sheets, because i need to use more than 255 columns (about 433). It looks like this:
    col1(ID) col2(Chain store category) col3(address) col4(date) col5-col255 (product's data). One product uses 9 columns like: space on a shelf, price, comments....

    Bacause there are more product's I need to report, I've spliited the data into 2 sheets. So the second sheet has the first 4 columns the same as in the first one (a copy) and from col5-col178 other product's data.

    When you create Pivot Table from multiple consolidation ranges, you
    won't get the same pivot table layout that you'd get from a single
    range. You can get only a field named "Rows" and one named "Columns" - in other words, it doesn't create fields named after the column headers .

    How my pivot table should look like?
    I need to calculate an average of one "parameter" (like price) for every product. Becacuse there are (for now) 47 products a pivot table can handle it. I need to present the results for every chain store type. I would like the data to be presented like:
    On the left (rows) there should be the chain store type (geant, tesco,....), on the top (columns) there shold be product's 1-47. And in the "data area" averages of the price (or other parameters).

    Creating such a pivot table from one data range is easy but can't it really be done using more data ranges? I can't use access or other database. It just must be done with excel. I've even tried importing "external data" (from the same file) with MS Query - and this should be some workaround....but not a perfect one.

    Please help! Thanks!

  2. #2
    Debra Dalgleish
    Guest

    Re: Pivot Table - consolidating ranges (again)

    You could create another worksheet, with the fields from the first two
    sheets that you want to use in the pivot table. Then, base the pivot
    table on the new worksheet's data.

    xman wrote:
    > This is my first post here...I hope that i've chosen the right
    > category....
    > My problem is similiar to other people's when it comes to consolidating
    > ranges with Pivot Table (sorry for my english...). I've searched the
    > forum but I haven't found either solution or workaround for my
    > problem.
    >
    > My data is divided into 2 sheets, because i need to use more than 255
    > columns (about 433). It looks like this:
    > col1(ID) col2(Chain store category) col3(address) col4(date)
    > col5-col255 (product's data). One product uses 9 columns like: space on
    > a shelf, price, comments....
    >
    > Bacause there are more product's I need to report, I've spliited the
    > data into 2 sheets. So the second sheet has the first 4 columns the
    > same as in the first one (a copy) and from col5-col178 other product's
    > data.
    >
    > When you create Pivot Table from multiple consolidation ranges, you
    > won't get the same pivot table layout that you'd get from a single
    > range. You can get only a field named "Rows" and one named "Columns" -
    > in other words, it doesn't create fields named after the column headers



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  3. #3
    Registered User
    Join Date
    09-16-2005
    Posts
    2

    My solution

    Thank you for your reply.
    At first I didn't like your solution, because it would make me create additional 9 sheets (there are already 10). I thought it would be difficult to browse through the sheets...
    But later I realized that these new sheets could be hidden! And this is the way I'm going to do it.
    I don't have much time to "fight with it". Maybe in 2 weeks time I will come back to this problem and find another solution....
    If so, I'll post my findings.
    Thank you again.
    Last edited by xman; 09-18-2005 at 04:10 AM.

  4. #4
    Debra Dalgleish
    Guest

    Re: Pivot Table - consolidating ranges (again)

    You're welcome! Thanks for letting me know how you're going to solve the
    problem (for now!)

    xman wrote:
    > Thank you for your reply.
    > At first I didn't like your solution, because it would make me create
    > additional 9 sheets (there are already 10). I thought it would be hard
    > to browse through the sheets.
    > But later I realized that these new sheets could be hidden! And this is
    > the way I'm going to solve it.
    > I don't have much time to "fight with it". Maybe in 2 weeks time I will
    > come back to this problem and find another solution....
    > If so, I'll post my findings.
    > Thank you again.
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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