1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sorting and presenting top values only once

Discussion in 'Ask an Excel Question' started by nircom, Sep 12, 2018.

  1. nircom

    nircom New Member

    Messages:
    9
    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)
    [​IMG]
    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/albums/mm36/nirosy/excel problem_zpschmoiasl.jpg

    Attached Files:

  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,053
    Hi, welcome to the forum.

    I'd recommend uploading sample workbook. It's bit tedious to recreate your data set from image. Help us help you, by uploading sample workbook that mirror your actual workbook (with desensitized data if needed).
    nircom likes this.
  3. nircom

    nircom New Member

    Messages:
    9
    Good idea. I've attached the workbook

    Attached Files:

  4. nircom

    nircom New Member

    Messages:
    9
    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....:-(
  5. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,053
    Interesting problem... hmm, are you open to VBA solution, or should it be formula based? I'm sure there's formula method, but I tend to resort to VBA for this sort of iterative calculation.

    Also, are you open to restructuring your raw data a bit? From multi-header with merged cells, to single header flat table.
    nircom likes this.
  6. nircom

    nircom New Member

    Messages:
    9
    Hi
    I know nothing about VBA so I can't really say if I'm open to it. I guess not :)
    Regarding restructuring the raw data, i think it's possible. might be a bit of work since the actual data I'm working on is 10X130 so basically it's 1300 cells.
  7. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,053
    Hmm, I'm having issue with formula. May be @bosco_yip , @John Jairo V or other formula experts can help.

    Ideally your raw data should look like attached.

    Attached Files:

    nircom likes this.
  8. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,007
    It can work for "Sheet 1" raw data layout but not for "Sheet 6" layout.

    Then,

    1] In "Sheet1" H5, copied down :

    =AGGREGATE(14,6,(SUBTOTAL(4,OFFSET($B$5:$D$5,ROW($B$5:$B$16)-ROW($B$5),))=INDEX($B$5:$D$16,,MATCH(G$3,$B$4:$D$4,0)))*INDEX($B$5:$D$16,,MATCH(G$3,$B$4:$D$4,0)),ROW($A1))

    2] In "Sheet1" G5, copied down :

    =INDEX($A$5:$A$16,MATCH(H5,INDEX($B$5:$D$16,,MATCH(G$3,$B$4:$D$4,0)),0))

    3] Select "Sheet1" G5:H7 >> Copy/paste to I5:J7 and K5:L7

    Regards
    Bosco

    Attached Files:

    Last edited: Sep 13, 2018
    Thomas Kuriakose, nircom and Chihiro like this.
  9. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    501
    Hi, to all!

    I think the second formula (in G5) could be changed to:

    =INDEX($A$5:$A$16,MATCH(H5,INDEX(INDEX($B$5:$D$16,,MATCH(G$3,$B$4:$D$4,))/(1-COUNTIF(G$4:G4,$A$5:$A$16)),),))

    In order to avoid a duplicate score for same person. Blessings!
    Last edited: Sep 13, 2018
    Thomas Kuriakose, nircom and Chihiro like this.
  10. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    453
    This was an attempt to capture the logic.

    Attached Files:

    nircom likes this.
  11. nircom

    nircom New Member

    Messages:
    9
    Thank you all.
    @bosco_yip in the file you uploaded theres a mistake.
    John's second grade should be History (85) and not Latin (79). Latin should be his third best grade. Not sure how to fix that.
  12. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    501
    Hi again to all!

    With your last post (#11) I think you use a different logic. I think this could be helpful:

    You could try:

    [G5]:
    =INDEX($A$5:$A$16,MATCH(H5,INDEX(INDEX($B$5:$D$16,,MATCH(G$3,$B$4:$D$4,))/(1-COUNTIF($G$4:$L4,$A$5:$A$16)),),))
    And Drag it down.

    [H5]:
    =AGGREGATE(14,6,INDEX($B$5:$D$16,,MATCH(G$3,$B$4:$D$4,))/(1-COUNTIF($G$4:$L4,$A$5:$A$16))/(1-COUNTIF($F$5:F5,$A$5:$A$16)),1)
    And Drag it down.

    Then, Select G5:H7, and drag it right until column L.

    Check file with the formulas. Blessings!

    Attached Files:

  13. nircom

    nircom New Member

    Messages:
    9
    thanks @John Jairo V but this creates multiple mistakes.
    For example, Daniel's best grade is now Chemestry (83) and it should be Bible (98)
  14. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    501
    Hello again!

    I think that then the logic is not correct, or at least it is not clear for me.

    The logic that I used for this new proposal is the following:

    1. I choose the best classes for each person, starting with John, which is Bible (95), then Daniel and then Mark (that is, it is chosen from left to right).

    2. As the Bible class already has John, and it is also the best for Daniel, then to Daniel I assign the second best, which chemistry (83), since you have said that they are unique for each person. As the best for Mark is maths (95).

    3. Once selected first classes for all people, now the next best ones are chosen. For John, the second best class is chemistry (91), but since Daniel already has it in his first class, then I choose his next class, which is History (85). And so on.

    In short, the logic I took was to choose the best of each independently in the sense of left to right, and then down, giving priority to who is more to the left.

    If it is another logic, comment it, because it is not clear then what you want to obtain. Blessings!
    nircom and Chihiro like this.
  15. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    453
    @john

    The logic that seemed to be working for me was to assign 1st place in each Class to the relevant student provided they had not exceeded their allotted 3 subjects.

    Subjects that were not assigned on the basis of 1st places were then open to assignment to another student. The 2nd in Class subjects can displace existing 1st in Class subjects if they are associated with a higher score.

    In theory, further iterations may be need to reassign 1st places that get displaced or 3rd places if appropriate.
    nircom likes this.
  16. nircom

    nircom New Member

    Messages:
    9
    First, let me apologize if the logic was not clear enough. I really appriciate the time you take to try to help me and solve this logical problem.

    Here is the logic in details:
    choose the best 3 grades for each student AND classes can't show up more than once. Also, each choice should reflect the highest grade in that specific class.

    So, let me describe the logical steps the best I can:
    1. We should look for the best overall grade first. We see that it's Daniel's Bible (98). this becomes Daniel's number 1 class.
    2. We look for the second best overall grade. We see that two grades show up. one belongs to Mark (95 in Math - this becomes Mark's number 1 class), and the second is John's (95 in Bible). Since Bible was already picked by Daniel, we ignore it completly.
    3. We look for the 3rd best overall grade. We see that we get two results: one is John's Chemestry (91) - this becomes John's number 1 class. Second is Mark's Science (91) - this becomes Mark's number 2 class (first one was Math).
    4. We look for the 4th best overall grade. We see that it's Mark again with Biology (88) - this becomes Mark's number 3 (and LAST) class.
    5. We look for the 5th best overall grade. We see that it's Mark again with History (87) - Since Mark already filled his 3 spots, we ignore it completly.
    6. We look for the 6th best overall grade. We see that it's John's History (85) - this becomes John's number 2 class.
    7. We look for the 7th best overall grade. We see that we get two results: First is John's Science (83) - we ignore this score beacuse Science already shows up in Mark's list. Second is Daniel's Chemestry (83) - we ignore this score beacuse Chemestry already shows up in John's list.
    8. We look for the 8th best overall grade. We see that it's Daniel's Biology (82) - Since Biology was already picked by Mark, we ignore it completly.
    9. We look for the 9th best overall grade. We see that it's Daniel's Economics (81) - this becomes Daniel's number 2 class.
    10. We look for the 10th best overall grade. We see that we get three results: First is John's Biology (80) - we ignore this score beacuse Biology already shows up in Mark's list. Second is Daniel's Grammer (80) - this becomes Daniel's number 3 (and LAST) class. Third is Mark's Chemestry (80) - Since Mark already filled his 3 spots, we ignore it completly.
    11. We look for the 11th best overall grade. We see that it's John's Latin class (79) - this becomes John's number 3 (and LAST) class.
  17. nircom

    nircom New Member

    Messages:
    9
    Attached is the file with the correct results highlighted in blue color (I entered it manualy)

    Attached Files:

  18. nircom

    nircom New Member

    Messages:
    9
    OK. got this sorted by a friend. here is the file for those of you who are interested. Many thanks for everyone.

    Attached Files:

  19. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    501
    Hi, to all!

    I got another option... you can check it and tell me if works.

    You can change cell I3 for top "n". Blessings!

    Attached Files:

    Thomas Kuriakose likes this.
  20. Haz

    Haz Active Member

    Messages:
    114
    And another one.

    Attached Files:

  21. Haz

    Haz Active Member

    Messages:
    114
    I've noticed my file wasn't accounting for duplicates, here's the fixed version.

    Attached Files:

    Thomas Kuriakose likes this.

Share This Page