Grouping Data fields in Pivot Tables.

[rttaksali]

Hi there,

I have recently started with Pivot tables. Below mentioned table is
the one on which I have to create a pivot table such that the average
order by a given customer/company for each furniture type in a year. I
can create the basic report with each furniture item showing its
average. I am interesting in grouping the furniture items namely as
indoor and outdoor furniture type. Indoor furniture type will contain
Wadrobe, Bed, drawers, coffee table and kitchen whereas outdoor
furniture type as chair and table. Is it possible to get the average
groupwise? Please, let me know it in detail as to how to go about it.

OrderDate CompanyName Town Wardrobe Bed Drawers CoffeeTable
Kitchen Chair Table
4-Nov-00 Mullens Ballycullen 0 0 8 0 5 0 0
2-Sep-00 Mullens Ballycullen 0 2 4 2 0 0 0
2-Jul-00 Mullens Ballycullen 4 2 4 6 0 12 3
3-Jun-00 Mullens Ballycullen 0 0 0 0 5 16 4
5-May-00 Mullens Ballycullen 0 0 4 4 0 8 0
1-Mar-00 Mullens Ballycullen 2 0 0 4 0 0 0
3-Jan-00 Mullens Ballycullen 0 4 8 0 0 0 0
2-Sep-00 Garden Glories Glencullen 0 0 0 0 0 16 4
10-Aug-00 Garden Glories Glencullen 0 0 0 0 0 12 3
12-Jul-00 Garden Glories Glencullen 0 0 0 0 0 24 6
6-Jun-00 Garden Glories Glencullen 0 0 0 0 0 8 2
2-May-00 Garden Glories Glencullen 0 0 0 0 0 24 4

Thanks

Rashi Gadia

 

[mukeshccu]

Hi there,

I have recently started with Pivot tables. Below mentioned table is
the one on which I have to create a pivot table such that the average
order by a given customer/company for each furniture type in a year. I
can create the basic report with each furniture item showing its
average. I am interesting in grouping the furniture items namely as
indoor and outdoor furniture type. Indoor furniture type will contain
Wadrobe, Bed, drawers, coffee table and kitchen whereas outdoor
furniture type as chair and table. Is it possible to get the average
groupwise? Please, let me know it in detail as to how to go about it.

OrderDate CompanyName Town Wardrobe Bed Drawers CoffeeTable
Kitchen Chair Table
4-Nov-00 Mullens Ballycullen 0 0 8 0 5 0 0
2-Sep-00 Mullens Ballycullen 0 2 4 2 0 0 0
2-Jul-00 Mullens Ballycullen 4 2 4 6 0 12 3
3-Jun-00 Mullens Ballycullen 0 0 0 0 5 16 4
5-May-00 Mullens Ballycullen 0 0 4 4 0 8 0
1-Mar-00 Mullens Ballycullen 2 0 0 4 0 0 0
3-Jan-00 Mullens Ballycullen 0 4 8 0 0 0 0
2-Sep-00 Garden Glories Glencullen 0 0 0 0 0 16 4
10-Aug-00 Garden Glories Glencullen 0 0 0 0 0 12 3
12-Jul-00 Garden Glories Glencullen 0 0 0 0 0 24 6
6-Jun-00 Garden Glories Glencullen 0 0 0 0 0 8 2
2-May-00 Garden Glories Glencullen 0 0 0 0 0 24 4

Thanks

Rashi Gadia

can u pls end the file to (e-mail address removed),so tat i can help ..

rgds,
mukesh

 

[Roger Govier]

Hi

Your data is not set out in an ideal way for Pivot Table analysis.
Rather than having Separate columns for Wardrobe, Bed etc. you should
have a single column called Furniture which contains Wardrobe, or Bed or
Kitchen etc. with another column alongside for Quantity.

Date Customer Town Furniture Quantity
04-Nov-00 Mullens Ballycullen Drawers 8
04-Nov-00 Mullens Ballycullen Kitchen 5

John Walkenbach has a routine for Converting data laid out as is yours,
into a format as I describe above.
http://j-walk.com/ss/excel/usertips/tip068.htm

You will need to run the routine 3 times, each time with the 8 columns
of data comprising your furniture items, along with the Date column,
then with the Customer column, then with the Town column (just cut and
paste the columns not been used to another part of the sheet).

Then, combine the data back together by just copying in the Town and
Customer columns alongside the data from your first pass.

This all sounds far more complicated than it actually is, and John has
images on his site which show very clearly what you are trying to
achieve and how to do it.

Once this has been completed, you can mark the Furniture Items you
consider Indoor, and right click>Group and Show Detail>Group
Repeat the procedure for the Outdoor Items.
You will find that Excel has created a new field called Furniture2. Drag
this to the Page area, and use the dropdown to select Group1 or Group2.
Whilst Group1 or Group2 are visible next to Furniture 2, you can
overtype with Indoor and Outdoor.