Nested Pivot Tables 1

[jnowles]

Can you nest pivot tables?

I have a spreadsheet in the following foumat:
ID SAMPLE r1 r2 r3 r4
1 434 6 7 7 6
1 837 5 5 6 6
2 434 7 6 4 4
2 837 7 7 8 8
3 434 7 8 8 8
3 837 9 9 9 8
4 434 7 6 8 7
4 837 8 8 7 6

and would like to transform it to look like:

ID 434 837
r1
1 6 5
2 7 7
3 7 9
4 7 8
r2
1 7 5
2 6 7
3 8 9
4 6 8
r3
1 7 6
2 4 8
3 8 9
4 8 7
r4
1 6 6
2 4 8
3 8 8
4 7 6

Can I accomplish this using pivot tables?

 

[mrsnrub]

I managed to create what you need using the data you provided. The steps I followed:

Create a Pivot Table with so that 'ID' is in the left area of the table layout (row section), 'Sample' across the top of the table layout (column section) and R1, R2, R3 and R4 in the centre (data section).

When the table was created, I dragged the Data column to the left of the ID column. I then used the Pivot Table options to turn off the Grand Total functionality. The data was then in the format as you described.

Hope this helps.

 

[jnowles]

That's perfect.

To push the envelope further, within r1,r2,r3,r4 can you calcutate col percent by sample and reading. For r1 and r2 the output would look like:
r1 434 837
5 0 25
6 25 0
7 75 25
8 0 25
9 0 25
r2 434 837
5 0 25
6 50 0
7 25 25
8 25 25
9 0 25