+ Reply to Thread
Results 1 to 7 of 7

Pivot Table, Filter, something else?

  1. #1
    Registered User
    Join Date
    10-13-2011
    Location
    Gibraltar
    MS-Off Ver
    MS Office 2007
    Posts
    3

    Pivot Table, Filter, something else?

    Hi guys, I was hoping you could help.

    I'm trying to put together a spreadsheet which, based on individual transaction information, will tell me which items sell most often (how many). I've played around with a Pivot Table and with Filters but haven't been able to crack it. I'm more likely to crack my head on the wall I've been banging it against!

    Attached is an example of my worksheet:

    It is a list of purchases made on various days over time, by different companies.

    What I need the sheet to do is look down the list of Item Codes, and for each item, return the total number sold.

    The last two columns were my attempts to do this by creating in the first a list of each individual item code and then using the adjacent cells in to add a formula which will lookup the item code and add together the sum of the relevant quantities. Having failed with that I tried using filters but whilst that seems to be a step in the right direction it still doesn't give me a summary of the total sold for each item. So then I heard of Pivot Tables and tried that but I really don't know enough about Filters or Pivots to pull this off.

    Could someone help please?

    Thanks.
    Attached Files Attached Files
    Last edited by Galant; 10-13-2011 at 06:30 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,023

    Re: Pivot Table, Filter, something else?

    You could sort the list and use Subtotals.

    Or you could use an Advanced Filter | copy unique entries to a new location to get a list of the products and then use SUMIF to get the totals ... or you may already have a list of products.

    A pivot table does sound the best way to go but that may depend on your data layout.

    Best option is to post a sample workbook.

    Regards
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    10-13-2011
    Location
    Gibraltar
    MS-Off Ver
    MS Office 2007
    Posts
    3

    Re: Pivot Table, Filter, something else?

    Thanks. I've attached a sample workbook.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,023

    Re: Pivot Table, Filter, something else?

    Looks like you need to update your profile.

    However, select a cell in the data area and, on the Insert Ribbon, choose Pivot Table, the first icon. Accept the range suggested, if OK, and choose where you want the Pivot Table to go.

    Drag Brand and Code to the Row Labels box; Drag Company to the Column Labels box; Drag Quantity to the Values box. Close the Pivot Table dialogue. Or, you could put Company in the Row box and Brand and Code in the Column box.

    It rather depends on how you want to see the data presented. But it does demonstrate the flexibility of Pivot Tables.

    Regards
    Last edited by TMS; 10-13-2011 at 07:48 AM.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,023

    Re: Pivot Table, Filter, something else?

    Thanks for the rep.

    If this has answered your question, please mark the thread as solved. See my signature for details or the FAQ.

    Regards

  6. #6
    Registered User
    Join Date
    10-13-2011
    Location
    Gibraltar
    MS-Off Ver
    MS Office 2007
    Posts
    3

    Re: Pivot Table, Filter, something else?

    Hi, no problem! I've been playing around with the table and it's exactly what I was after. So thank you again. As for closing the thread I had seen your signature and will close it shortly. Just one more question first though. I've noticed that there are two 'brand' categories that have appeared and I can't figure out why.

    There is one listed as (blank) but I've checked and can't see any cells where 'Brand' is empty. There is also one other which appears to be a duplicate of another brand although it has only one entry underneath it. I have checked for formatting errors but can't see any.

    My first question is, is there anyway to get the Excel to show me the source cell(s) for figures appearing in the table? That way I could see exactly which cell seems to be causing the trouble.

    My second question is, how do I fix this?

    Thanks.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,023

    Re: Pivot Table, Filter, something else?

    The only thing I can think of is that you have selected a range with some blabk rows or cells in it.

    In the example I provided based on the sample data, there are no duplicates or blanks and the PT seems to represent the data in the table.

    Usually, things like that point to values with a space or two on the end. Th easiest way to find oddities like that is to use an autofilter and, where you have two entries for what should be one, you need to investigate.

    Regards

+ 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