Hey there, I came across an issue with my sheet I use to fluidly update team rankings. I have column B in the chart below set to update as results come in and then the sheet sorts and ranks them and displays the name of the team with the highest score listed in first place in column F and so on... My issue is that when two teams have the same score, only one team is listed for both places/rankings. How can I fix that to show both team names? Even if it ranks them first by score and tie scores are then ranked secondarily by alphabet. Is this possible or should I use a different formula altogether? This is the current formula I use for column F: =INDEX(A2:A19,MATCH(SORT(B2:B19,1,-1),B2:B19,0))
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Ranking Issue
- Thread starter kcross31
- Start date
Eric W
MrExcel MVP
- Joined
- Aug 18, 2015
- Messages
- 12,980
Try:
| Book1 | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Teams | Points | Place | Current Team Standings | ||||
| 2 | Blue | 1 | 1 | Lime Green | ||||
| 3 | Red | 2 | 2 | Hot Pink | ||||
| 4 | Green | 3 | 3 | Silver | ||||
| 5 | Yellow | 14 | 4 | Neon | ||||
| 6 | Pink | 6 | 5 | Purple | ||||
| 7 | Purple | 15 | 6 | Yellow | ||||
| 8 | Gold | 7 | 7 | Bronze | ||||
| 9 | Silver | 16 | 8 | Brown | ||||
| 10 | Orange | 8 | 9 | Black | ||||
| 11 | White | 9 | 10 | White | ||||
| 12 | Black | 11 | ||||||
| 13 | Brown | 12 | ||||||
| 14 | Bronze | 13 | ||||||
| 15 | Magenta | 4 | ||||||
| 16 | Teal | 5 | ||||||
| 17 | Neon | 16 | ||||||
| 18 | Hot Pink | 17 | ||||||
| 19 | Lime Green | 18 | ||||||
| 20 | ||||||||
Sheet15 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2:F11 | E2 | =LET(s,SEQUENCE(10),CHOOSE({1,2},s,INDEX(SORT(A2:B19,2,-1),s,1))) |
| Dynamic array formulas. | ||
Upvote
0
Solution
That's got it! Not sure if I'm allowed to ask a follow up question but is there anyway to then have those teams who are tied to be highlighted someway so I can see there was a tie?
Upvote
0
Never mind, I figured that out. Thanks again!
Upvote
0
