Pivot tables - duplicated row headings

[Landmark]

Dear all, I hope someone can help me. I have a worksheet of data
showing description down the left side, months across the top, and
amounts in the data fields, it look ssomething like the little table
below. Note how I have several columns for each month, as the forecast
is broken down into weeks.

Aug-05 Aug-05 Sep-05
Wk 3 Wk 4 Wk 5
Product 1 10
Product 2 20
Product 3 5 30
Total 15 20 30


I want to create a pivot table and a pivot graph to show the total
sales for August, the total for September 05 etc. But when I create my
pivot using the product descriptions to form the rows, and the month
headings to form the data, I end up with a table that shows me colomns
of data entitled Aug 05, Aug 052, Sep 05, Sep 052, Sep 053, Sep 054 etc
etc.

How can I get round this, can I somehow easily tell the pivot table to
group all the Aug 05 into one column and give me one total?

Kind regards

 

[Peo Sjoblom]

It won't work with that layout, for help on pivots see:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

and

http://www.contextures.com/tiptech.html

 

[Landmark]

Hi Peo, many thanks for your reply, and I have looked at both of the
sites you have listed, both very useful, but although they address
grouping of periods, it seems to be dependant on the periods being
listed in one column vertically, rather than accross several colunns
horizontally.

However, I cant beleive that it wont be possible to somehow group my
periods by either week, month or quarter, bearing in mind so often
accounting source data is presented in this format, ie products down
the side and periods across the top spanning several columns..

There must be a way!!!

 

[Landmark]

Also, I have seen a pivot tutorial that seems to suggest it is possible
(although it is a bit out of date - from 1996!!!),
http://lacher.com/examples/lacher27.htm

 

[Peo Sjoblom]

But it looks like you have 2 headers, one for month, then below wk and below
that the data? Did you look at the source data in that example
Also in the link I posted there is a section about multiple consolidation
ranges

http://www.contextures.com/xlPivot08.html

 

[George Nicholson]

Okay, I've learned something new today (from your pointer to
http://lacher.com/examples/lacher27.htm), let me see if I can walk you
through this....

1) Make sure your month columns headers contain actual dates. How they
display does not matter, but the actual values must be dates: (i.e., 9/1/05,
not the text value 9-05). AFAIK, this is necessary for Excel to do grouping.

2)
Data>PivotTableandChartReport>*MultipleConsolidationRanges*>CreateASinglePageFieldForMe
3) on Step 2b of this wizard, Select your SourceData, including column
headers and row labels. Click "Add", then Next.
4) Tell Excel where to put your table

This should give you what you want, with your 4 weeks combined into a single
month column. If it doesn't, try right-clicking on the grey "Column" data
marker and try to force a group by month. (GroupAndShowDetail>Group>select
"Months")

HTH,

 

[Landmark]

Dear Peo and George

Many thanks to both of you, Peo, I see now that your link to the
multiple consolidated ranges (as described in the walk through by
George) were indeed just want I needed, but that didnt quite click till
I saw Georges step by step guide. So thanks to you both, and glad you
also learnt something new George!!

Only one more question I have is if I wanted to show more than one set
of rows in my table instead of just the very left hand one that excel
displays by default (with all other "rows" being automatically asigned
to the colunm section) is that possible?

I really appreciate both of your help - thanks so much, and happy
christmas

 

[Debra Dalgleish]

For a pivot table created from multiple consolidation ranges, there's
only one field.

 

[Landmark]

OK, thanks for confirming that Debra

Happy new year