Rank duplicate text values

Question

Looked around, but could not quite get this to work. I think it's really easy, but match and index functions are tripping me up.

Here's the example. I have three columns of data:

Name    Date    Score Desired Output 
Peter   8/11    80        Wrong Day 
John    8/12    50        1 
Peter   8/12    40        1 
Sarah   8/12    30        1 
Peter   8/12    20        2 
Lindsay 8/12    5         1 
Peter   8/12    3         3

See desired output. In short, I want to look at the list (I've sorted it by Score) and give me for all scores belonging to one person, the rank of that score on the desired day. So I want to look at all people on one day (8/12 in this case) and then group the scores by person, and return the rank of the score for that person. I would know anything with a "1" is the top score for that person on 8/12.

Any ideas? Would rather not use PivotTable. Want a formula for column D

tigeravatar tigeravatar · Accepted Answer · 2014-07-29T00:20:34

In cell D2 and copied down (adjust ranges to suit your actual data):

=IF(B2=--"8/12",MATCH(C2,INDEX(LARGE(INDEX(($A$2:$A$8=A2)*($B$2:$B$8=--"8/12")*$C$2:$C$8,),ROW($1:$99)),),0)+COUNTIFS($A$1:$A1,A2,$B$1:$B1,--"8/12",$C$1:$C1,C2),"Wrong Day")

If you have a cell that you're entering the desired date into, replace all instances of --"8/12" with the cell reference containing the date. Make sure you use absolute references $ if you do so.

If you could have more than 99 rows for a single day and person, increase the ROWS($1:$99) so that the 99 is a large enough number to accomodate the maximum number of rows for a single day and person.

The COUNTIFS portion of the formula is for tie-breakers, so that subsequent identical numbers will be ranked lower (with a higher number) instead of ranked the same. For example, if the second Peter entry on 8/12 was also 40 instead of 20, the formula would still populate that row as 2 instead of having a tied rank at 1.

Rank duplicate text values

1 Answers