# 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.
Nircom
http://i292.photobucket.com/albums/mm36/nirosy/excel problem_zpschmoiasl.jpg

#### Attachments

• 90.6 KB Views: 13

#### Chihiro

##### Excel Ninja
Hi, welcome to the forum.

#### nircom

##### New Member
Good idea. I've attached the workbook

#### Attachments

• 15.1 KB Views: 11

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

#### Chihiro

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

#### Attachments

• 10.2 KB Views: 6

#### 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

• 17.3 KB Views: 10
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:

#### Peter Bartholomew

##### Well-Known Member
This was an attempt to capture the logic.

#### Attachments

• 21.6 KB Views: 7

#### 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

• 11.6 KB Views: 2

#### nircom

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

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

#### nircom

##### New Member
Attached is the file with the correct results highlighted in blue color (I entered it manualy)

#### Attachments

• 17.5 KB Views: 5

#### nircom

##### New Member
OK. got this sorted by a friend. here is the file for those of you who are interested. Many thanks for everyone.

#### Attachments

• 23.7 KB Views: 7

#### John Jairo V

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

#### Attachments

• 17.4 KB Views: 7

And another one.

#### Attachments

• 18.5 KB Views: 5

#### Haz

##### Active Member
And another one.
I've noticed my file wasn't accounting for duplicates, here's the fixed version.

#### Attachments

• 19.2 KB Views: 5