Pivot Table Query

[Joe Miller]

Hi, I'm not sure if this is the exact forum for this post, but it looks like the closest one

I'm working with a Pivot Table based on a dataset that contains three fields which I'm trying to manipulate into a particular table. Basically, the fields are Location, Type and Date. The values of the first and last fields are self explanatory. The values of the Type field will be either A, B or C. Basically I have data for 10 locations over three months on various dates which is of one of the three types. I'm comfortable in creating a table that will display the occurance of categories A, B and C for each location for the entire 3 months in one go, but what I really want to do is break that three months up so I can see the occurrance of A,B and C for each area in January, Feb, March etc

At the moment the question of more than one year is not important, however I do have a collection of historical data that goes back 3 years and I would really like to be able to perform the above task with this also, but in a way so that January won't contain Jan 02,03 and 04

I'm not sure how much sense this makes, but I'd be really greatful if one of you clever people could point me in the right direction

Thank
Jo

 

[Debra Dalgleish]

Create a pivot table with Date and Location in the Row area, Type in the
Column area, and Date again in the Data area, where it will become
Count of Date.

In the pivot table, right-click the Date field, and choose Group and
Show Detail > Group.
Group the date by Year and Month, click OK

 

[Joe Miller]

Hi

Thanks for the response - I've tried doing this and all I get is a message saying "Cannot group that selection

It might help to add that I'm running Excel 200

Jo

 

[Debra Dalgleish]

If your date field includes blank cells, or cells with text, you won't
be able to group the dates.