Pivot Table, Filter, something else?

[Galant]

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.

[TMS]

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

[Galant]

Thanks. I've attached a sample workbook.

[TMS]

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

[TMS]

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

[Galant]

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.

[TMS]

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