Pivot Query  
Author Message
quandry





PostPosted: Tue Apr 24 19:05:43 CDT 2007 Top

worksheet functions >> Pivot Query

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

Excel22  
 
 
Roger





PostPosted: Tue Apr 24 19:05:43 CDT 2007 Top

worksheet functions >> Pivot Query 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

--
Regards

Roger Govier




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