Tailoring Pivot Table Data
 

Good morning, All!
I've got a pivot table that I need to format for import in MS Project.

How do I turn pivot table data that appears like this:

WBS Desc Resource YYYYMM Total
1.0 Mgmt Engr 200811 20
200812 20

Into data that appears like this?

WBS Desc Resource Start Finish Total
1.0 Mgmt Engr 200811 200812 40

Thanks for your help!

Tailoring Pivot Table Data
 

Does your data consistently have only two lines (i.e. Start and Finish
Dates)? Are the dates within on month and year? If not how many
maximum lines it may have?
Aqib Rizvi

Tailoring Pivot Table Data
 

Hello Aqib,
No, in some cases there are multiple dates and they cross multiple years,
like this:

WBSID Desc Resource YYYYMM Total
1.1.0 Reporting Engr 200811 10
200812 20
200901 15
200902 5
200903 10
200904 15

So, I need the Max date, the Min date and the Total summed.
Thanks for helping me out!


"Aqib Rizvi" wrote:

Does your data consistently have only two lines (i.e. Start and Finish
Dates)? Are the dates within on month and year? If not how many
maximum lines it may have?
Aqib Rizvi

Tailoring Pivot Table Data
 

You will need to add two more fields to your source data to calculate the Min
and MAx of each group. You can use an array formula similar to this

=MAX(IF($A$2:$A$100="That", $B$2:$B$100))
Must be committed with Shift + Ctrl + <Enter
--
HTH...

Jim Thomlinson


"Blueglass" wrote:

Hello Aqib,
No, in some cases there are multiple dates and they cross multiple years,
like this:

WBSID Desc Resource YYYYMM Total
1.1.0 Reporting Engr 200811 10
200812 20
200901 15
200902 5
200903 10
200904 15

So, I need the Max date, the Min date and the Total summed.
Thanks for helping me out!


"Aqib Rizvi" wrote:

Does your data consistently have only two lines (i.e. Start and Finish
Dates)? Are the dates within on month and year? If not how many
maximum lines it may have?
Aqib Rizvi

Tailoring Pivot Table Data
 

What would be entered for "That"?

"Jim Thomlinson" wrote:

You will need to add two more fields to your source data to calculate the Min
and MAx of each group. You can use an array formula similar to this

=MAX(IF($A$2:$A$100="That", $B$2:$B$100))
Must be committed with Shift + Ctrl + <Enter
--
HTH...

Jim Thomlinson


"Blueglass" wrote:

Hello Aqib,
No, in some cases there are multiple dates and they cross multiple years,
like this:

WBSID Desc Resource YYYYMM Total
1.1.0 Reporting Engr 200811 10
200812 20
200901 15
200902 5
200903 10
200904 15

So, I need the Max date, the Min date and the Total summed.
Thanks for helping me out!


"Aqib Rizvi" wrote:

Does your data consistently have only two lines (i.e. Start and Finish
Dates)? Are the dates within on month and year? If not how many
maximum lines it may have?
Aqib Rizvi