Pivot Table: Return Value From Same Row
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 |
Pivot Table: Return Value From Same Row
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 |
Pivot Table: Return Value From Same Row
Typos: F2, G2 should have read as F4, G4 in the earlier descript
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Pivot Table: Return Value From Same Row
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 --- |
Pivot Table: Return Value From Same Row
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 |
Pivot Table: Return Value From Same Row
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 |
All times are GMT +1. The time now is 02:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com