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





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*:

In G2, array-entered*:
Copy F2:G2 down

*Press CTRL+SHIFT+ENTER to confirm the formula,
instead of just pressing ENTER

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"

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

Thanks for answering so quickly.




Sean Smart

Solved it!! This solution is also posted here:

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 A1:D10
7) Add Critical Location to the Data area
8) Weep with joy...


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question