Hi Everyone-
This time I am trying to expand on my original spreadsheet. I still seem to have problems ranking (sorting) when there are duplicate values in a column.
I have been able to use what I have learned to made a sorted list of players for the league, but I need to be able to do the same thing for each team. For my example data, I have just continued to enlarge the spreadsheet to the right. In reality, these results will be placed elsewhere and used for further VLOOKUPs.
So far I have tried several INDEX-MATCH and OFFSET-MATCH combinations without success. It seems to me that I need an IF statement or something with SUMPRODUCT, like what was used for the PlayerTeamStandingsUnique (and what I still don’t fully understand).
Thanks for all your help, past and present.
Ron
The expanded sample data is as follows:
ColA
PlayerName
Player1
Player2
Player3
Player4
Player5
Player6
ColB
TeamName
TeamA
TeamB
TeamB
TeamA
TeamA
TeamB
ColC
PlayerScore
48
43
55
48
38
46
ColD
PlayerLeagueStanding
=RANK(C3,$C$3:$C$8) [Copy Down]
Results:
ColD
PlayerLeagueStanding
2
5
1
2
6
4
ColG
PlayerLeagueStandingUnique
=RANK(C3,$C$3:$C$33)+COUNTIF(C$3:C3,C3)-1 [Copy Down]
Results:
ColG
PlayerLeagueStandingUnique
2
5
1
3
6
4
ColE
PlayerTeamStanding
=SUMPRODUCT(--($B$3:$B$8=B3),--($C$3:$C$8>C3))+1 [Copy Down]
Results:
ColE
PlayerTeamStanding
1
3
1
1
3
2
ColF
PlayerTeamStandingUnique
Formula =(SUMPRODUCT(--($B$3:$B$8=B3),--($C$3:$C$8>C3))+1)+(SUMPRODUCT(--($B$3:B3=B3),--($C$3:C3=C3))-1) [Copy Down]
Results:
ColF
PlayerTeamStandingUnique
1
3
1
2
3
2
NEW FROM HERE:
ColG
LeaguePlayerStandings
Formula =OFFSET(A$3,MATCH(SMALL($E$3:$E$8,ROW()-ROW(A$13)+1),E$3:E$8,0)-1,0) [Copy Down]
Results:
ColG
LeaguePlayerStandings
Player3
Player1
Player4
Player6
Player2
Player5
ColH
TeamAPlayerStandings
Formula = ????????? [Copy Down] NOTE: To simplify the formula the total number of players on the team is known and can be entered as a constant into the formula.
Results should be:
ColH
TeamAPlayerStandings
Player1
Player4
Player5
ColI
TeamBPlayerStandings
Basically the same formula as ColH, but modified as needed for total number of players.
Results will be:
ColI
TeamBPlayerStandings
Player3
Player6
Player2
Bookmarks