Home |
Search |
Today's Posts |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Grouping Data fields in Pivot Tables.
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 clickGroup and Show DetailGroup 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. -- Regards Roger Govier wrote in message oups.com... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
grouping data in pivot tables | Excel Discussion (Misc queries) | |||
grouping in pivot tables | Excel Discussion (Misc queries) | |||
Pivot table grouping of dates in row fields will not work. Why? | Excel Worksheet Functions | |||
Grouping Dates in Pivot tables | Excel Discussion (Misc queries) | |||
Pivot Tables multiple data fields | Excel Worksheet Functions |