+ Reply to Thread
Results 1 to 3 of 3

Pivot Table adding additional columns excel 2007

  1. #1
    Forum Contributor
    Join Date
    04-19-2010
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    106

    Pivot Table adding additional columns excel 2007

    I have a worksheet as below:

    PHP Code: 
    store #          UPC            Key        Category     Sales Units
    123               555            123-555     soda           20
    124               222            124
    -222     candy         10  
    123               555            123
    -555     soda           15 
    I want the pivot table to return:


    PHP Code: 
    store #          UPC            Key        Category     Sales Units
    123               555            123-555     soda           35
    124               222            124
    -222     candy         10 
    I created the pivot to combine duplicates by location and UPC, so I created a the "key" column to do that: =a1&"-"&b1

    The challenge I have in excel 2007 is I cannot get the Category as a column in the pivot, when I include it, I get a subtotal on every key, that is not what I want, I want the actual category of that UPC, I know I can perform a lookup to the original worksheet, but there has to be an easier way?
    Last edited by runner; 09-19-2012 at 01:09 PM.

  2. #2
    Forum Contributor
    Join Date
    04-19-2010
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    106

    Re: Pivot Table adding additional columns excel 2007

    I just performed some Google research on this one, it seems that excel 2007 overlooked the capability of having more than one descriptive column populate the pivot table, you can only have one column to look up from, any other column populated has to be numeric. I can trick the pivot on the numeric description in the workseet, but he alpha columns have to be brought back in later with a lookup, that is pretty crappy, I can think of all sorts of reasons why you'd need to include with the UPC/Store lookup, the category, subcategory, addresses, market name, market number, etc..., etc....

    Does anyone know if 2010 is any better?

    Disclaimer: I think the essence of existing in everything we do/know/learn is on google now, right?

  3. #3
    Forum Contributor
    Join Date
    04-19-2010
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    106

    Re: Pivot Table adding additional columns excel 2007

    I figured out a way to get the descriptives in the pivot table, choose pivot table tools, design, tabular, remove subtotals, but then it does not add any duplicates, it is basically the same spreadsheet.......hmmmmm

+ 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