• 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...

  • 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

nircom

New Member
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)

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
 

Attachments

Chihiro

Excel Ninja
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

New Member
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....:-(
 

Chihiro

Excel Ninja
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

New Member
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.
 

bosco_yip

Excel Ninja
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
 

Attachments

Last edited:

John Jairo V

Well-Known Member
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:

nircom

New Member
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.
 

John Jairo V

Well-Known Member
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!
 

Attachments

John Jairo V

Well-Known Member
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!
 

Peter Bartholomew

Well-Known Member
@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

New Member
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.
 
Top