Rank with criteria

[ORoxo]

Hey, guys,
I'm really going crazy with this as I have not been able to figure it out for a couple of days.
Let's take the following picture as an example:

I'm basically looking for a formula on input on column D which does the exact same thing but with one exception. Let's say we on row 21 we have data exactly as we do on row 19. In that case, I want the formula to return the "2" as it did on row 19.

Any idea how this can be achieved?

 

[ORoxo]

Guys, I could really use some help here, please?

 

[Aladin Akyurek]

In D2 control+shift+enter, not just enter, and copy down:

=IF(A2<>A1,IF(COUNTIFS($B$2:B2,B2,$C$2:C2,C2)=1,MATCH(B2,LARGE(IF(FREQUENCY(IF($C$2:$C$32=C2,MATCH($A$2:$A$32,$A$2:$A$32,0)),ROW($A$2:$A$32)-ROW($A$2)+1),B$2:$B$32),ROW(INDIRECT("1:"&SUM(IF(FREQUENCY(IF($C$2:$C$32=C2,MATCH($A$2:$A$32,$A$2:$A$32,0)),ROW($A$2:$A$32)-ROW($A$2)+1),1))))),0),LOOKUP(BigNum,1/(($B$1:B1=$B2)*($C$1:C1=C2)),$D$1:D1)+1),INDEX($D$1:D1,MATCH(A2,IF($C$1:C1=C1,$A$1:A1),0)))

I think the desired ranks you posted for P&R are not correct. Please t​

ry to evaluate the proposal meticulously (i.e. avoid a hasty reaction).

 

[Aladin Akyurek]

Edit:

BigNum is defined as 9.99999999999999E+307.

 

[RockandGrohl]

We are soooo soooo close...

The only problem with this is if there are two distinct students in the same department with the same grade. Assuming there are two students with the same top grade, in that case, the formula should rank one of them as 1 and the other one as 2. However, its currently ranking both as 1.

Note to self: I reallt need to study FREQUENCY function

This is how I got around this particular problem, assuming you still need help:

=SUMPRODUCT(($A$4:$A$5069=A4)*($O$4:$O$5069+ROW($O$4:$O$5069)/10000>=O4+ROW(O4)/10000))

A:A is name (you can have student name here) O:O is the score.

Replace the column index numbers and it should work. It doesn't produce a similar rank, even if you had a list of 50 scores of 25.6, it would give them a distinct rank from 1-50 with no duplicates.

This will mean that some students with an equal rank are placed below the other, but such is life.