Identify highest score category within group

  • Hi All,

    I have a data table from column A-D. I want to identify the category in which each student has highest marks.

    For some data manipulation purpose, I would like to have the category printed on a row which had the highest marks for a particular student.

    Attached is my data table and I have manually typed the end result that I want to achieve by formula. Please note that there are varying rows as per students, E.g. Alex has 3 rows, Bob has 2 and Kim has 4. If the formula can print the result in a same row which had highest marks per student it would be great.

    Appreciate your time.

    Best,

  • Re: Identify highest score category within group


    Try this Array* formula:


    [COLOR="#0000FF"]=IF(D2=INDEX($D$2:$D$10,MIN(IF(($A$2:$A$10=A2)*($C$2:$C$10=MAX(IF($A$2:$A$10=A2,$C$2:$C$10))),ROW($A$2:$A$10)-ROW($A$2)+1))),INDEX($D$2:$D$10,MIN(IF(($A$2:$A$10=A2)*($C$2:$C$10=MAX(IF($A$2:$A$10=A2,$C$2:$C$10))),ROW($A$2:$A$10)-ROW($A$2)+1))),"")[/COLOR]


    [arf]*[/arf]


    copied down

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Identify highest score category within group


    MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    *

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!