I'm not sure that a pivot could return the result sets that you seek.
I'd just work off the pivot data like this
Assuming the pivot source as posted is in A3:C11 (data within A4:C11)
with the group#s listed in E4 down, ie: 1,2,3
you could place
In F2, array-entered*:
=MAX(IF($A$4:$A$11=E4,$B$4:$B$11))
In G2, array-entered*:
=INDEX($C$4:$C$11,MATCH(MAX(IF($A$4:$A$11=E4,$B$4: $B$11)),IF($A$4:$A$11=E4,$B$4:$B$11),0))
Copy F2:G2 down
*Press CTRL+SHIFT+ENTER to confirm the formula,
instead of just pressing ENTER
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sean Smart" wrote:
I'm using a pivot table to analyse some data but am a bit stuck at the
moment. I'd like to have the pivot table find, for a group, the maximum of a
column (Depth), return that value and also return the value from the same row
but from a different column (Location).
Group Depth Location
------------------------------
1 0.1 a
1 0.2 b
1 0.1 c
2 0.1 d
2 0.3 e
3 0.5 f
3 0.2 g
3 0.4 h
Results should be:
Group Max Depth Location
----------------------------------------
1 0.2 b
2 0.3 e
3 0.5 f
Thanks,
Sean