 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / March 2008Pivot 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 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  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > 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 [quoted text clipped - 22 lines] > Thanks, > Sean Typos: F2, G2 should have read as F4, G4 in the earlier descript SignatureMax 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 > Typos: F2, G2 should have read as F4, G4 in the earlier descript Welcome, Sean SignatureMax 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 [quoted text clipped - 8 lines] > Cheers, > Sean 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 A1:D10 7) Add Critical Location to the Data area 8) Weep with joy... > Welcome, Sean > > Hi Max, [quoted text clipped - 9 lines] > > Cheers, > > Sean |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.