Sorting and Matching question

[madmanmac]

I love baseball trivia and am using excel to sort for some stuff to put in a baseball trivia book that I published last year and am updating for another edition some day.

Here is what I need to know how to do........

I have imported home run data into Excel for each player of each team for each year. I have sorted this data by team and then by year but I want a formula that will go through the list and pick out the top player for each team for each year.

Example, I want to break out the Yankees top home run hitter for 2004, 2005 and 2006, while ignoring the other players in those years that were not the team leader. Obviously, two or more players can tie for the top spot.

Hope this makes sense. Is this something that can be done easily? The data file is 31,000+ lines long.

Thanks!



Player Team Year HR
Sammy Sosa Cubs 2001 64
Andruw Jones Braves 2005 51
Sammy Sosa Cubs 2000 50
Albert Pujols Cardinals 2006 49
Sammy Sosa Cubs 2002 49
Albert Pujols Cardinals 2004 46
Derrek Lee Cubs 2005 46
Javier Lopez Braves 2003 43
Albert Pujols Cardinals 2003 43
Jim Edmonds Cardinals 2000 42
Jim Edmonds Cardinals 2004 42
Andruw Jones Braves 2006 41
Albert Pujols Cardinals 2005 41
Sammy Sosa Cubs 2003 40
Gary Sheffield Braves 2003 39
Jim Edmonds Cardinals 2003 39
Moises Alou Cubs 2004 39
Chipper Jones Braves 2001 38
Aramis Ramirez Cubs 2006 38
Albert Pujols Cardinals 2001 37
Chipper Jones Braves 2000 36
Andruw Jones Braves 2000 36
Andruw Jones Braves 2003 36
Aramis Ramirez Cubs 2004 36
Andruw Jones Braves 2002 35
Sammy Sosa Cubs 2004 35
Andruw Jones Braves 2001 34
Albert Pujols Cardinals 2002 34
Scott Rolen Cardinals 2004 34

[raypayette]

Click Data, Filter, Aotofilter
or click Data, Sort, Descending

[olasa]

I sorted the Pivottable so the rank can be read bi-directional - team and year.
I also tried it on 35.000 (fake) rows and there was no recalculation delay.

I hope it's close to what you were hoping for.
//Ola


Pivottables:
The headlines in the pivottable can be moved to suit your want for layout.
To learn more about pivottables. Try these short free videos: http://www.datapigtechnologies.com/ExcelMain.htm

Array formula:
The formula is an array formula so type the formula and then hold down Ctrl and Shift and then hit Enter, all at the same time. That will produce the squiggly brackets - indicating that it has been typed correctly - and is now an array formula.

[madmanmac]

Wow! That is outstanding and exactly what I need. I don't know much about pivot tables but I need to learn so that I can use this method on the overall file which is much larger.

Thanks for your time........