Transpose two columns of a three column pivot table

[bmorro]

Hi All.

I'm using Excel 2003 and want to transpose the data fields and associated values from the vertical format to the horizontal format with the associated values that correspond to each unique record under each column heading.

My method was to copy the pivot table with the spacing between each unique record and use the =transpose formula to pull in the correct data.

I was wondering if there was a quicker way as there are 2000+ unique records.

 

[RonB1111]

Within the pivot table, left click on the data field name and simply "drag" it to the columns area (just to the right and slightly above where they are now) and release the left click button. They will automatically move and display horizontally.

 

[bmorro]

Unfortunately, the suggested "left-click and drag" keeps returning an error: "You cannot change, move a part of, or insert cells in a pivot table report. If you want to insert cells on the worksheet, drage the entire report out of the way..."

Here is the example I was referring to.

<TABLE style="WIDTH: 227pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=303 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 137pt; mso-width-source: userset; mso-width-alt: 6692" width=183><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #e0dfe3" class=xl22 height=20 width=64>Property ID</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 137pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #e0dfe3" class=xl22 width=183>y</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl23 width=56>Total</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #e0dfe3" class=xl22 height=20>TDC0001</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #e0dfe3" class=xl22>Sum of Rentable Area</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl26 x:num>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl24 height=20> </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl25>Sum of Usable Area</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: black 0.5pt solid" class=xl27 x:num>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl24 height=20> </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl25>Sum of Gross Area</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: black 0.5pt solid" class=xl27 x:num>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl24 height=20> </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl25>Sum of Gross Measured Area</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: black 0.5pt solid" class=xl27 x:num>0</TD></TR></TBODY></TABLE>

 

[RonB1111]

Using your example, drag the "y" into "Total"

 

[bmorro]

RonB111,

Thank you very much. Finally worked for me.

 

[RonB1111]

You're welcome. Thank you for the feedback.