Pivot Table: Return Value From Same Row

[Sean Smart]

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

 

[Max]

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]

Typos: F2, G2 should have read as F4, G4 in the earlier descript

---

 

[Sean Smart]

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]

Welcome, Sean

 

[Sean Smart]

Solved it!! This solution is also posted here:
http://www.mrexcel.com/forum/showthread.php?p=1521068#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",A2),C2,"")
5) Fill down
6) Change the pivot table data range to A110
7) Add Critical Location to the Data area
8) Weep with joy...