Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2012
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Grouping rows with same values into pivot table?

    Hi, I'm fairly new to my role at work and have just started using pivot tables.
    I'm working with data from a few months ago that i'm trying to pool together into one table.

    For example, I have tables like the ones below:

    What I want to do is make a master table that gives me totals from all of the months for each company. However, every month does not have the same companies, so I guess my primary concern is is excel capable of figuring out which ones are the same and grouping them together, and keeping the others separate?
    May-11
    Company Sales
    Walmart 400
    Costco 200
    CVS 59
    Walgreens 28

    Jun-11
    Company Sales
    Walmart 244
    CVS 582
    Kmart 284
    Eckerds 189

    Jul-11
    Company Sales
    Walmart 843
    Sears 138
    Kmart 457
    Target 284


    I want a master table that would include something like this, but would be tallied automatically:

    Company SALES
    Walmart 1487
    Costco ###
    CVS ##
    Walgreens ###
    Kmart ###
    Eckerds ##
    Sears ##
    Target ##

    I tried making a pivot table, combining the separate tables i had of each month, however excel tallies based on the rows, not the actual company name.


    Any help will be greatly appreciated! thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you supply an example workbook of what you have and perhaps an example of the summary desired?Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    yawan (2012-01-25)

  4. #3
    New Lounger
    Join Date
    Jan 2012
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Sample data

    Sample score card data.xls

    Here's an example of the data i have, and the type of data i need to be generated.

    Thanks!

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    see attached file.

    I 'copied' your blocks of data to Sheet2, so that they were 'under' each other rather than alongside.

    Then, you can now use a formula to fetch your results..
    I put the formulas alongside your existing data on sheet1.
    As you can see, they return the required numbers.

    zeddy
    Attached Files Attached Files

  6. The Following User Says Thank You to zeddy For This Useful Post:

    yawan (2012-01-25)

  7. #5
    New Lounger
    Join Date
    Jan 2012
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Not a pivot, but this actually does the work! Thanks!!

  8. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    A pivot also works, and will update (when refreshed) to add new names added to the list.

    Steve
    Attached Files Attached Files

  9. The Following User Says Thank You to sdckapr For This Useful Post:

    yawan (2012-01-25)

  10. #7
    New Lounger
    Join Date
    Jan 2012
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Ah, that makes more sense now... all of the data needs to be in the same columns...

  11. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    And with the pivot you do have the option to also separate out by year, month, quarter, etc if desired...

    Steve

Tags for this Thread

Posting Permissions

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