Counting the first and second best ranks for a ranking spreadsheet

[swik78]

Hi All,
I am struggling with getting this to work using Excel formulas. If anyone can help, would be highly appreciated!
I have a table that stores Matches between players and records the Winners/ Losers Name. It also shows the State Ranking of the Losing Player.
Now I need to create a list with the names of all players and show the number of wins and losses along with recording the Rank of the best player they beat.

Example: Match records
[TABLE="width: 427"]
<tbody>[TR]
[TD]Player1
[/TD]
[TD]Player2
[/TD]
[TD]Winner
[/TD]
[TD]Loser
[/TD]
[TD]Winner Rank
[/TD]
[TD]Loser Rank
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD]John
[/TD]
[TD]Tom
[/TD]
[TD]John
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Lewis
[/TD]
[TD]Ron
[/TD]
[TD]Ron
[/TD]
[TD]Lewis
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD]Ron
[/TD]
[TD]Dave
[/TD]
[TD]Ron
[/TD]
[TD]Dave
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Lewis
[/TD]
[TD]James
[/TD]
[TD]James
[/TD]
[TD]Lewis
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]James
[/TD]
[TD]Tony
[/TD]
[TD]Tony
[/TD]
[TD]James
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Tony
[/TD]
[TD]Ron
[/TD]
[TD]Ron
[/TD]
[TD]Tony
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]6
[/TD]
[/TR]
</tbody>[/TABLE]


The list I am trying to create is as follows. Is there a way to create a BestWin Rank for each player? I am fine with counting the #Wins and #Losses.
I guess this needs to loop through all the Wins for that player and then in that subset return the rank of the best player beaten.

Sorry, If I havent explained this well! Any help/ pointers will be greatly appreciated! Thanks!


[TABLE="width: 235"]
<tbody>[TR]
[TD]
[TABLE="width: 971"]
<tbody>[TR]
[TD]Player
[/TD]
[TD]#Wins
[/TD]
[TD]#Losses
[/TD]
[TD]BestWin Rank
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ron
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]
[TD]???2
[/TD]
[TD="colspan: 8"] i.e The best Rank of Players that Ron beat above i.e Smallest of (5,2,6 ) which is 2
[/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD]??
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dave
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD]?
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tony
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lewis
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]