I have a spreadsheet which tracks the all time results of my fantasy baseball league. I have a table that displays the top 10 in all of the categories we track (Runs, RBI, HR, etc).
I was able to get the list to show the top 10 using the LARGE function then used Index(Match) to obtain the Name, Week, and Year. But in some cases there are duplicate values where 2 teams have the same score and I want to display those individually. Also, the incorrect name is showing up. For example, The person who scored 56 runs was not TJ, it was Bryja in week 11 of 2019. So my Index(Match) formula is grabbing the wrong person's name.
For example:
Dahl is repeated twice and there is another person who also scored 52 runs in Week but they aren't showing up.
Here is a sorted table of what the data SHOULD look like. I created a column for a "code" thinking I could use that somehow to display these values uniquely but I can't seem to figure it out.
This is the formula used to obtain the Large value for the "Runs" column.
=LARGE(IF('All Games'!$W$2:$W$9662="N",'All Games'!$B$2:$B$9662,0),1)
Col B is the number of runs
Col W is a designation of a "long week" or a regular week. I only want to display the numbers for the regular weeks and exclude long weeks from the score.
I was able to get the list to show the top 10 using the LARGE function then used Index(Match) to obtain the Name, Week, and Year. But in some cases there are duplicate values where 2 teams have the same score and I want to display those individually. Also, the incorrect name is showing up. For example, The person who scored 56 runs was not TJ, it was Bryja in week 11 of 2019. So my Index(Match) formula is grabbing the wrong person's name.
For example:
| Most Runs in a Week | ||||
| Name | Runs | Week | Year | |
| 1st | TJ | 56 | 1 | 2011 |
| 2nd | DAHL | 54 | 19 | 2011 |
| 3rd | DAHL | 53 | 21 | 2010 |
| 4th | DAHL | 52 | 22 | 2010 |
| 5th | DAHL | 52 | 22 | 2010 |
Dahl is repeated twice and there is another person who also scored 52 runs in Week but they aren't showing up.
Here is a sorted table of what the data SHOULD look like. I created a column for a "code" thinking I could use that somehow to display these values uniquely but I can't seem to figure it out.
| PLAYER | YEAR | WK | Lng Wk | RUNS | Code |
| BRYJA | 2019 | 11 | N | 56 | BRYJA201911 |
| BRYJA | 2017 | 15 | N | 54 | BRYJA201715 |
| MEYERS | 2019 | 11 | N | 53 | MEYERS201911 |
| MEYERS | 2020 | 7 | N | 52 | MEYERS20207 |
| BRYJA | 2022 | 16 | N | 52 | BRYJA202216 |
| DRISH | 2010 | 14 | N | 51 | DRISH201014 |
| MEYERS | 2019 | 8 | N | 51 | MEYERS20198 |
| WHITE | 2021 | 2 | N | 50 | WHITE20212 |
| WHITE | 2023 | 17 | N | 50 | WHITE202317 |
| NORG | 2019 | 18 | N | 49 | NORG201918 |
This is the formula used to obtain the Large value for the "Runs" column.
=LARGE(IF('All Games'!$W$2:$W$9662="N",'All Games'!$B$2:$B$9662,0),1)
Col B is the number of runs
Col W is a designation of a "long week" or a regular week. I only want to display the numbers for the regular weeks and exclude long weeks from the score.
