Pivot Query

[Fred]

Using Excel 2002, SP3, I have a large spreadsheet of data, laid out
roughly as follows
Project Name, Team Name, Service Provided, # of services provided (12
times for Jan thru Dec)
Some of the teams provide only consultancy, whereas others provide
consultancy and other deliverables as well, eg Apples, Bananas and
Pears or Nuts, Bolts and Washers.
What I would like to get is a report, by Team, listing the Projects
and then the Deliverable (Consultancy, Apples, Bananas &/or Pears) and
then the number for each month. I would like to get a "subtotal" of
each deliverable for each team

Team Project Del Jan Feb Mar Apr May Jun
A 1 Cons 5 3 4 5 7 1
Apples 2 2 2
2 Cons 5 1 1 1 4
Pears 2
2 2
3 Cons 3 3 3 2
Bananas
6 6
4 Cons 3 2 2
======================================
Total Cons 5 8 8 12 13
9
Apples 2 2 2
Bananas 6 6
Pears 2 2 2
======================================
B 1 Cons 1 3 5 7

Nuts 2
Bolts 2
2
2 Cons 2 2 3 3
Nuts 1 2
3
Washers 2 4 6
======================================
Total Cons 2 1 5 8 10
Nuts 1 2 5
Bolts 2 2
Washers 2 4 6
======================================

Repeated for Team C, D etc who may be working on the same or different
projects, all with Consultancy but with different other deliverables,
e.g. Nuts, Bolts and Washers.

Apologies for the repost, I missed the groups of the first posting.

Thanks in advance for any help
Regards
Fred

 

[Roger Govier]

Hi Fred

I think you need to convert your data first, into a format suitable for
Pivoting.
Rather than 12 columns for months, you need 2 columns, 1 with Month
name, the other with Month vale.

John Walkenbach has a great technique for converting data of your type
into regular PT type data layout.
Take a look at
http://j-walk.com/ss/excel/usertips/tip068.htm