CODEWITHSUNDEEP
excelexcel-formula
Bob Bob
Bob Bob

Reputation: 25

Creating a Top Ten list in Excel

I have been searching for a way to make a "Top Ten" list for my uncles hockey league in Excel.

There are 5 different teams with their own worksheets and I made a separate worksheet called "data" referencing all of the players and their individual stats so that I could pull my "Top Ten" from one worksheet instead of five.

The worksheet is setup as follows:

    Player1  Player2  Player3  etc... 
Name

Team

Goals

Assists

Points

I would like to search for the player with the highest number of "Points" and return the name, team, goals, assists, and points on a single row, and then move down for the next player, i.e.:

Name 1    Team     Goals     Assists    Points

Name 2    Team     Goals     Assists    Points

I have tried using multiple ways including index and hlookup with the large and match commands, but could not get anything to work. I feel like I am missing something simple here.

If it would be easier to have the players in rows, I could do that. I was just having a problem with my references filling in the way I wanted them to.

Upvotes: 1

Views: 2945

Answers (2)

Jüri Ruut
Jüri Ruut

Reputation: 2530

Initial table:
B1: =COUNTA(B6:O6)(the number of players) B6:O6 Name
B7:O7 Team
B8:O8 Goals
B9:O9 Assists
B10:O10 Points

Formulae below determine the rank for the player. If the score of points are equal, the player having a greater number of goals ranks higher.
B4: =$B$1-RANK(B$10;$B$10:$O$10)+B8/1000, the formula is copied to the right. The result will be fractional numbers with equal integer parts for the equal amount of points.
B3: =RANK(B4;$B$4:$O$4), the formula is copied to the right
B2: =IF(COUNTIF(C$3:$O$3;B3)>0;B3+COUNTIF(C$3:$O$3;B3);B$3)

Ranked table:
A15 and down : Rank (1,2...n)
B15 and down : =HLOOKUP(A15;$A$2:$O$3;2;FALSE)

In the next columns: Name (col C), Goals (col D), Assists (col E), Points (col F)

C15: =HLOOKUP($A15;$A$2:$O$10;5;FALSE)
D15: =HLOOKUP($A15;$A$2:$O$10;7;FALSE)
E15: =HLOOKUP($A15;$A$2:$O$10;8;FALSE)
F15: =HLOOKUP($A15;$A$2:$O$10;9;FALSE)

Formulae in B11:E11 will be copied down.

Example spreadsheet: http://www.bumpclub.ee/~jyri_r/Excel/Top_players_matrix_transposed.xls

Upvotes: 1

sourcecode
sourcecode

Reputation: 1802

you have to transpose the table first..

  • select all the rows and columns of your table.
  • right click the first cell where you want to paste the table
  • click paste special option
  • check the transpose check-box in the dialog-box opened.

your table is now transposed. now simple sorting can give u answer

for sorting columns :

  • select the whole table and click on the sort option on menu bar,

now you can retrieve all 10 top teams from this sorted list.

Upvotes: 0

Related Questions