Pivot tables - duplicated row headings

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Landmark - 14 Dec 2005 16:36 GMT

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

Reply to this Message

Peo Sjoblom - 14 Dec 2005 16:57 GMT

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

Signature

Regards,

Peo Sjoblom

> 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
[quoted text clipped - 20 lines]
>
> Kind regards

Reply to this Message

Landmark - 14 Dec 2005 17:38 GMT

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!!!

Reply to this Message

Landmark - 14 Dec 2005 17:40 GMT

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

Reply to this Message

Peo Sjoblom - 14 Dec 2005 17:58 GMT

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

Signature

Regards,

Peo Sjoblom

> 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

Reply to this Message

George Nicholson - 14 Dec 2005 18:44 GMT

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,

Signature

George Nicholson

Remove 'Junk' from return address.

> 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

Reply to this Message

Landmark - 22 Dec 2005 11:13 GMT

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 :)

Reply to this Message

Debra Dalgleish - 22 Dec 2005 14:19 GMT

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

> Dear Peo and George
>
[quoted text clipped - 11 lines]
> I really appreciate both of your help - thanks so much, and happy
> christmas :)

Signature

Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply to this Message

Landmark - 30 Dec 2005 15:04 GMT

OK, thanks for confirming that Debra

Happy new year

Reply to this Message