sorting and presenting top values only once

    Hi All,
    I'm having a problem and I hope you can help.
    If you look at the raw data, you see grades of 3 students in 10 different classes.
    I want to write a formula that picks the best 3 grades of each student in each class (this means each class can be presented only once)
    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"excel problem.JPG","data-attachmentid":1208236}[/ATTACH]
    For example, John's best grade is in Bible (95) but Daniel has a better grade in Bible (98) so the results will ignore John's grade in Bible and will look for his second highest grade (in this case it's Chemistry).
    I've calculated how the results should look like (table on the right side) but this was done without formula since it's too complicated for me.
    I hope someone here can tell me how the formula should look like.
    Thanks in Advance,
    Nircom


    http://i292.photobucket.com/al…20problem_zpschmoiasl.jpg

    Hello,


    Sadly a picture is an inert object ...


    As an indication, you could have in cell I5 the following formula

    Code
    =LARGE($B$4:$B$20,1)


    and in cell H5 this formula :

    Code
    =INDEX(A:A,MATCH(I5,B:B,0))


    It would much better for you to attach your workbook with your next message ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Thank you Carim.
    The problem with the formula you suggested is that it does not take into account the grades of the other students.
    For example, the second best grade for Daniel is Chemistry (83), but it should not be presented because John has a better grade in Chemistry (91).
    I've attached the workbook.

    Hello again,


    Thanks for the workbook ...


    Sorry for having initially read your question too quickly ...


    You do mention 10 Classes .. and I do count 12 Unique Names ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    As you can see, John's best grade is Bible (95), but it should NOT be presented because of two reasons:
    1). Daniel has a better grade in Bible (98)
    AND
    2). Daniel's grade in Bible is one of Daniel's top 3 grades.
    On the other side, John's third best grade is History (85). This grade is presented in spite the fact that Alan has a better grade in History (87). This is because History is not part of Alan's top 3 grade.
    I hope it's clearer now.
    I don't understand how to write this formula....:

    Well ... Not sure to fully understand all the rules ...


    As far as the first four classes : Bible 98 - Math 95 - Chemistry 91 - Science 91 ... I can follow the logic ...


    But afterwards, my guess would have been Biology 88 and History 85 ....


    Thanks for you added clarification ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hello again,


    Just to try understand the underlying logic ... and before jumping on designing rather complex formulas ... have created 3 steps for your review ...in the attached test file ...


    Hope this will help

    Quote from nircom


    You got this exactly right!


    I have made a mistake earlier...


    What should be done now?


    Quite honestly ... I do not know ... :facepull:


    At least ... we agree on what the Final Result should look like ...:lol:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    oh, wow. at least I now know that it's not a simple matter of my lack of understanding.
    Thanks very much for trying Carim!


    IS THERE ANY ONE ELSE IN THIS FORUM THAT CAN HELP SOLVE THIS PROBLEM?

    As soon as I have a moment ... I will dive into the intricacies of your problem ... just to get all the steps in an orderly manner ... :wink:


    By the way ... have you set your mind on a formula based solution versus a macro ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    I don't know how to write Macro , but if there's a Macro solution that will be presented here, I think I'll be able to use it.

    Ideally, once you get to your computer you should modify all your Classes and Grades to play along with this Test file ...
    and see if you can validate all the formulas ... :wink:


    Quite honestly ... I haven't had the time to change the input data and make simulations ...


    Thanks for your Thanks .. AND for the Like ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi I've looked at the file. I addmit I dont understand all that you did, but I've played with the data in order to make sure it outputs correct results, and there seems to be a problem. For example, when I change Daniels Bible grade to 81 or less, it creates an error (82 or above is fine). I cant trace the reason.

    Hi,


    It would appear that such a Grade of 81 or less ... does impact on Alan's third class ... since all his own good grades appear in classes already listed for both Daniel and John ... :wink:


    You can see the details in the Range W10:AF10 ...


    Is it what you are expecting or not ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re,


    Apparently ... if any given student gets the same Grade twice or more ... it does disrupt all the Rankings ...:rambo:


    Let me design a simulation worksheet to scan all the possibilities... and will get back to you as soon as possible ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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

Register Yourself Login