Pivot table question

[bwlytkr]

I am trying to build a pivot table that will sum up the total number of Transfer items in the Wa Cd column and total the # of actual reported hours for the entire Job Number. If I filter on the transfer item it will only total the number of transfers. Is this possible in a pivot table? Below is a sample of the data. As you can see the job numbers have hours on multiple lines but only 1 transfer item for each job. Any help would be greatly appreciated. Thanks John



[TABLE="width: 494"]
<colgroup><col width="64" style="width: 48pt;" span="2"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <col width="64" style="width: 48pt;"> <col width="56" style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;"> <col width="137" style="width: 103pt; mso-width-source: userset; mso-width-alt: 5010;"> <tbody>[TR]
[TD="width: 64, bgcolor: #0B64A0"]State Cd[/TD]
[TD="width: 64, bgcolor: #0B64A0"]Cmc Cd[/TD]
[TD="width: 90, bgcolor: #0B64A0"]PWO Category[/TD]
[TD="width: 79, bgcolor: #0B64A0"]Job Nbr[/TD]
[TD="width: 104, bgcolor: #0B64A0"]WC Name[/TD]
[TD="width: 64, bgcolor: #0B64A0"]District Nm[/TD]
[TD="width: 56, bgcolor: #0B64A0"]Wa Cd[/TD]
[TD="width: 137, bgcolor: #0B64A0"]Actual Reported Hours[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"]FL[/TD]
[TD="bgcolor: #F8FBFC"]BRVC [/TD]
[TD="bgcolor: #F8FBFC"]POLE [/TD]
[TD="bgcolor: #F8FBFC"]PWO5L3841[/TD]
[TD="bgcolor: #F8FBFC"]TITUSVILLE [/TD]
[TD="bgcolor: #F8FBFC"]FL NORTH [/TD]
[TD="bgcolor: #F8FBFC"]END[/TD]
[TD="bgcolor: #F8FBFC"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]FL[/TD]
[TD="bgcolor: white"]BRVC [/TD]
[TD="bgcolor: white"]POLE [/TD]
[TD="bgcolor: white"]PWO5L3841[/TD]
[TD="bgcolor: white"]TITUSVILLE [/TD]
[TD="bgcolor: white"]FL NORTH [/TD]
[TD="bgcolor: white"]PLAC[/TD]
[TD="bgcolor: white"]1.75[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"]FL[/TD]
[TD="bgcolor: #F8FBFC"]BRVC [/TD]
[TD="bgcolor: #F8FBFC"]POLE [/TD]
[TD="bgcolor: #F8FBFC"]PWO5L3841[/TD]
[TD="bgcolor: #F8FBFC"]TITUSVILLE [/TD]
[TD="bgcolor: #F8FBFC"]FL NORTH [/TD]
[TD="bgcolor: #F8FBFC"]REMO[/TD]
[TD="bgcolor: #F8FBFC"]0.75[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]FL[/TD]
[TD="bgcolor: white"]BRVC [/TD]
[TD="bgcolor: white"]POLE [/TD]
[TD="bgcolor: white"]PWO5L3841[/TD]
[TD="bgcolor: white"]TITUSVILLE [/TD]
[TD="bgcolor: white"]FL NORTH [/TD]
[TD="bgcolor: white"]TRNSFR[/TD]
[TD="bgcolor: white"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"]FL[/TD]
[TD="bgcolor: #F8FBFC"]BRVC [/TD]
[TD="bgcolor: #F8FBFC"]POLE [/TD]
[TD="bgcolor: #F8FBFC"]PWO6N7004[/TD]
[TD="bgcolor: #F8FBFC"]TITUSVILLE [/TD]
[TD="bgcolor: #F8FBFC"]FL NORTH [/TD]
[TD="bgcolor: #F8FBFC"]END[/TD]
[TD="bgcolor: #F8FBFC"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]FL[/TD]
[TD="bgcolor: white"]BRVC [/TD]
[TD="bgcolor: white"]POLE [/TD]
[TD="bgcolor: white"]PWO6N7004[/TD]
[TD="bgcolor: white"]TITUSVILLE [/TD]
[TD="bgcolor: white"]FL NORTH [/TD]
[TD="bgcolor: white"]PLAC[/TD]
[TD="bgcolor: white"]2.5[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"]FL[/TD]
[TD="bgcolor: #F8FBFC"]BRVC [/TD]
[TD="bgcolor: #F8FBFC"]POLE [/TD]
[TD="bgcolor: #F8FBFC"]PWO6N7004[/TD]
[TD="bgcolor: #F8FBFC"]TITUSVILLE [/TD]
[TD="bgcolor: #F8FBFC"]FL NORTH [/TD]
[TD="bgcolor: #F8FBFC"]REMO[/TD]
[TD="bgcolor: #F8FBFC"]2.5[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]FL[/TD]
[TD="bgcolor: white"]BRVC [/TD]
[TD="bgcolor: white"]POLE [/TD]
[TD="bgcolor: white"]PWO6N7004[/TD]
[TD="bgcolor: white"]TITUSVILLE [/TD]
[TD="bgcolor: white"]FL NORTH [/TD]
[TD="bgcolor: white"]SPL[/TD]
[TD="bgcolor: white"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: #F8FBFC"]FL[/TD]
[TD="bgcolor: #F8FBFC"]BRVC [/TD]
[TD="bgcolor: #F8FBFC"]POLE [/TD]
[TD="bgcolor: #F8FBFC"]PWO6N7004[/TD]
[TD="bgcolor: #F8FBFC"]TITUSVILLE [/TD]
[TD="bgcolor: #F8FBFC"]FL NORTH [/TD]
[TD="bgcolor: #F8FBFC"]TRNSFR[/TD]
[TD="bgcolor: #F8FBFC"]5.5[/TD]
[/TR]
</tbody>[/TABLE]

 

[bbotzong]

You mean like this?

Sum of Actual Reported Hours Column Labels
Row Labels END PLAC REMO SPL TRNSFR Grand Total
PWO5L3841 - 1.8 0.8 4.0 6.5
PWO6N7004 - 2.5 2.5 2.0 5.5 12.5
Grand Total - 4.3 3.3 2.0 9.5 19.0

If so, COlumns is Wa Cd, Rows is Job Nbr, and Values is sum of Actual Reported Hours.

 

[bwlytkr]

Thanks but I am trying to put it in a summary format since I have regional data with 1000's of lines of data. Is it possible to do something similar to below but only pull the transfer data in the Wa Cd column but give me the total hrs for the job

[TABLE="width: 373"]
<colgroup><col width="120" style="width: 90pt; mso-width-source: userset; mso-width-alt: 4388;"> <col width="131" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4790;"> <col width="246" style="width: 185pt; mso-width-source: userset; mso-width-alt: 8996;"> <tbody>[TR]
[TD="width: 120"]Row Labels[/TD]
[TD="width: 131"]Count of Wa Cd[/TD]
[TD="width: 246"]Sum of Actual Reported Hours[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]FL[/TD]
[TD="bgcolor: transparent, align: right"]8910[/TD]
[TD="bgcolor: transparent, align: right"]26467.5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"](blank)[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]8910[/TD]
[TD="align: right"]26467.5[/TD]
[/TR]
</tbody>[/TABLE]