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

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

Typos: F2, G2 should have read as F4, G4 in the earlier descript
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi Max,
I went for a pivot table because I have 15,000+ rows to analyse and arrays
are pretty slow (I've had some boring times watching the "calculating cells"
percentage).

I might give a crack at Access as soon as the IT guys let me have it :-)

Thanks for answering so quickly.

Cheers,
Sean

"Max" wrote:

Typos: F2, G2 should have read as F4, G4 in the earlier descript
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Welcome, Sean
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sean Smart" wrote in message
...
Hi Max,
I went for a pivot table because I have 15,000+ rows to analyse and arrays
are pretty slow (I've had some boring times watching the "calculating
cells"
percentage).

I might give a crack at Access as soon as the IT guys let me have it :-)

Thanks for answering so quickly.

Cheers,
Sean

Solved it!! This solution is also posted he
http://www.mrexcel.com/forum/showthr...68#post1521068

At the moment it only works if the Location column is a number e.g.

Group Depth Location
---------------------
1 0.1 10
1 0.2 20
1 0.1 30
2 0.1 40
2 0.3 50
3 0.5 60
3 0.2 70
3 0.4 80

How to do it:
1) Create a pivot table with range A1:C10, at H1
2) Add Group to row area
3) Add Depth to Data area (summarise by maximum)
4) In D1 I added a header called Critical location, then in D2 added the
following formula:=IF(B2=GETPIVOTDATA("Depth",$H$1,"Group",A 2),C2,"")
5) Fill down
6) Change the pivot table data range to A1:D10
7) Add Critical Location to the Data area
8) Weep with joy...

"Max" wrote:

Welcome, Sean
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sean Smart" wrote in message
...
Hi Max,
I went for a pivot table because I have 15,000+ rows to analyse and arrays
are pretty slow (I've had some boring times watching the "calculating
cells"
percentage).

I might give a crack at Access as soon as the IT guys let me have it :-)

Thanks for answering so quickly.

Cheers,
Sean