Forum Discussion

TheOnlyNaveen's avatar
TheOnlyNaveen
Copper Contributor
Jun 16, 2022

Ranking Help needed for unsorted data

Hi I have data similar to what is in Colums A, B & C.

My objective is to get data(Ranking) in column D as seen in the image. Can you guys please help me.

Basically what i want to get is Ranking of scores for each subject for a particular Student. The data here is sorted in order but in my database the data is not sorted too, so thats an additional difficulty.

eg. Student A has scored 198/200 in subject 4, so that is Ranked 1 and next rank 2 is for subject 3 in which he scored 180/200

In similar way i want to rank marks scored in various subjects for a particular Student. Can someone help me out???

Question: Is there a single formula that i can copy & drag in column D so that it assigns Ranks automatically, than me to have to sort and apply Rank formula after filtering each name.

excel
    • TheOnlyNaveen's avatar
      TheOnlyNaveen
      Copper Contributor
      Jun 16, 2022
      Yeah I got what you did here but i want it on the data set so that I can then apply a Pivot on the base data to show only the Top 2/3 ranked Subject for each student.

      If I do it as per your method, I will not be able to apply a universal Top2/3 rank filter.

      Apologies I didn't explain properly earlier what is the end result I wanted & thanks for the effort to reply to my message
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jun 16, 2022

        Hi TheOnlyNaveen 

         

        If you format your range as a Table you won't have to copy down + it's highly recommended if you need a Dense Rank. The formula is column F performs "intensive" array calcs. so limiting the arrays to the actual used "range" is quite important

         

        Assuming above Table1

        in E3:

        =COUNTIFS([Name],[@Name], [Marks],">"&[@Marks])+1

         in F3:

        =SUM(
    --(
        FREQUENCY(
            ([Name]=[@Name])*([Rank]<[@Rank])*[Rank],
            ([Name]=[@Name])*([Rank]<[@Rank])*[Rank]
        ) > 0
    )
)

Resources