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