1

I'm working on a football database of weekly scores. Each row represents an individual's weekly performance (e.g. catches, yards, TDs), while also containing their weekly fantasy score, and their season total fantasy score.

Each record now needs a Position Rank (within each year), meaning where their season score ranked within their football position for each year. Highest scoring QB in 1995 is QB 1, seventh highest RB from 2008 is RB 7.

A           B               C       D           E       F               G           ...
UniqueID    NameAdj         Season  SeasonPts   Pos     PosRank_RB      PosRank_QB  ...

1           Corey Dillon    1997    198.8       RB                      
2           Jerry Butler    1979    108.7       WR                      
3           Earl Campbell   1978    227.8       RB                          
4           Bo Jackson      1987    105.0       RB

Each position will have its own Pos Rank column, and I created this formula to rank the Season Points:

=COUNTIFS($c:$c,$c2,$e:$e,$e2,$d:$d,">"&$d2)+COUNTIFS($c$2:$c2,$c2,$e$2:$e2,$e2,$d$2:$d2,$d2)

Problem is, because each player may have 16 games (e.g. 16 records), that means Corey Dillon may have 16 records where Season=1997 AND SeasonPts=198.8. So he'll take up 16 places in the rankings.

I need to ignore those records when both $B2 AND $D2 are duplicates.

P.S. I wouldn't mind producing this with two options: one column where only the first 1997 Corey Dillon gets a rank number, and one where all 1997 Corey Dillons share duplicate rank. I'm guessing as I move forward into running COUNTIF (and other formulas) on all this data, having both columns could prevent future headaches (e.g. "Number of Seasons where..." could get spiked by 16 Dillons).

Dave M
  • 51
  • 8
  • Can the Unique ID be used to pull a single record or would it be duplicated along with the other data? –  Jul 12 '15 at 05:40
  • There are ~199,950 records / rows of data, and each has a UniqueID. If Corey Dillon played 10 seasons, then he would have ~10x16=160 records with 160 unique IDs for his 160 weeks played. – Dave M Jul 12 '15 at 15:18
  • It seems that not only is the individual player's score for the year to be considered unique, the scores they rank against must be pared down to be unique for the year.While marginally possible to do this on smaller data sets with formulas, ~200K records makes a formula-based result impractical. A run-on-demand macro could handle it in a several minutes or you could prepare for the future by converting to SQL and ranking individual scores over unique score sets with a partition based query. There is also the question of whether any handicaps are given to players with less than 16 games. –  Jul 12 '15 at 16:09

0 Answers0