Excel 2007 : Automatically group rows in text only table

[spjmorris]

Hi all - first post - Merry Christmas!!

hoping someone can help with this, been struggling for a while now, automatically grouping rows together.

I do a report for a customer detailing when a product hits a value of 1 or 0 in our main warehouse, what I would love to add to the report is the ability for the customer to be able to drill down and show availability at our other sites around the country

picture paints a thousand words!

xl.jpg

first table is example of base data - there are also other column to the right to show usage over time

warehouse 1 must always be on show along with the description qty etc, group buttons will expand to show other locations
I can do it manually but it's just not practical as there can be anywhere up to 10,000 items just in warehouse 1, is there a way to automate this?

thanks :-)

I also tried doing this with a pivot set to tabular but it only shows the totals and hides descriptions so no good at all

cheers again!

[Richard Buttrey]

Hi,

I don't see why the Pivot Table can't be made to show descriptions as well. I'd persevere with it. A PT also allows you to easily drill down to the underlying records.

Pictures may paint many words but on the forum we generally prefer workbooks!

[MarvinP]

Hi spjmorris and welcome to the forum,

I believe a Pivot Table is your best bet. When you double click on a pivot table value it will open a new sheet showing a Drill Down of the records that make up that sum. See the attached with some fake data but the warehouse as a column. Will this do it for you? http://chandoo.org/wp/2010/01/27/pivot-table-tricks/

Another possible tool is Advanced Filters if you need to display all text. http://www.contextures.com/xladvfilter01.html

[spjmorris]

Richard, Marvin - thanks for the quick replies!

Richard - found chandoo recently and been trawling through the site - will take a look at the link you have posted

workbook is attached, thanks for taking the time to look at this , I've added some comments which I hope will explain things more clearly, and also a sample of the real workbook

my initial thinking was a pivot but I just cant get it to display as I need it to.

really want to avoid the customer having to double click pivots and opening up new sheets, would like to have it all on one sheet ideally
would also like not to have to do depart too much from what they already receive, they don't like change....

thanks, Steve

[spjmorris]

I now realise the title of this thread is completely misleading...

[Richard Buttrey]

Hi Steve,

Is one option to show the warehouse options in the Report Filter area above the Pivot Table - see attached?

[Marcol]

picture paints a thousand words!

..... and nobody is particularly keen on typing them all again!

Best to post a sample of your workbook.

[spjmorris]

Marcol - first time here, didn't realise I could attach the file at first, will know for next time!
Richard - filters on the warehouse is definitely an option - might be the only way go

thanks all for the suggestions

Merry Christmas!

Steve

[MarvinP]

Hi Steve,

This looks more like an inventory problem all the time. I'm going back to Pivot Tables and include a date column. Then the qty can show negative for product sold and positive for restocking numbers. In the attached pivot table the dates go in the column and GROUPED BY MONTH. The qty can be changed to a running total instead of simple count. For a description you can do a VLookup to the left of the SKU field of the pivot table if needed. See the example.