Identifying data in one column based on highest figure data in another column

[Doofus1]

Hi Folks,
I have attached an excel file for the question.

I am trying to write a formula that shows the best grade for each each student and the subject in which
that grade was achieved.

In a previous post i was shown how to identify the highest grade
MAX(IF(A$3:A$34=A7,C$3:C$34))

I hit CTRL, SHIFT & ENTER to activate the formula - Result in column E

However, I also want to identify the subject in which they scored their highest grade in column F

I have two problems
1. I don't know how to write a formula that brings in the subject based on the grade for each student

2. I don't know how to write a formula in case there is a tie (see DAVID L)

Any help would be appreciated.
Thank You

[Tony Valko]

What version of Excel are you using? Please update your forum profile. This helps us determine the best solution for your application.

What should happen when there are ties for the highest grade?

[Doofus1]

Hi Tony,
Sorry.
I am using Excel 2010.
Thank You

[Tony Valko]

OK, what should happen when there are ties for the highest grade?

[Doofus1]

If there were two exact times eg in a100m race, eg 9.97 on 1/1/2014, and 9.97 on 12/1/2013, for the same runner, I would want to select the best time off the most recent race.

However, in this case, given that it is text, I would like to have the two or three subjects appear together in the same cell - like i have typed out in column F for David L.

Any suggestions?
Thank you

[Tony Valko]

Ok, I see now that column F is the result.

To concatenate multiple results in the same cell will require a VBA function.

Are you open to that?

[Doofus1]

I have uploaded a file with another example too


OK.
Willing to try it - but remember that I am not that smart.
Will this slow the document down ?

Also, what would the formula be like if we were dealing only with digits - in the example of the 100m race ?
Thank you

[Pete_UK]

This array* formula in F3:

=INDEX(B$3:B$34,MATCH(1,(A$3:A$34=A3)*(C$3:C$34=E3),0))

will get the name of the subject of the first highest-scoring grade.

*Use Ctrl-Shift-Enter (CSE) to confirm the formula, then copy down.

So, it will not return the names of the second, third subject etc. in the event of a tie.

Hope this helps.

Pete

[Doofus1]

Sorry Pete.
This IS theorarically what i want.
The gibberish below can be disregarded.
Thank you


Hi Pete,
Thank You.
However, if I was going to do that, i would want the most recent high fugure as opposed to the earliest.
How would i do that for the most recent highest scoring grade?
But this is good to know if the issue comes up in reverse.
Thank You

[Tony Valko]

I have uploaded a file with another example too


OK.
Willing to try it - but remember that I am not that smart.
Will this slow the document down ?

Alright, here's the VBA function to solve problem 1.

With your file open...

Press the key combination of Alt F11 to open the Visual Basic Editor

Goto the menu Insert>Module

Copy the VBA code at the link below and paste it into the window that opens on the right side of the screen

http://www.excelforum.com/showthread.php?p=3096647

Press the key combination of ALT Q to close the Visual Basic Editor and return to Excel.

Then, enter this array formula** in F3:

=concatall(IF(A$3:A$34=A3,IF(C$3:C$34=MAX(IF(A$3:A$34=A3,C$3:C$34)),B$3:B$34,""),""),", ")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down as needed.

You'll have to save the file as a macro enabled file in the *.xlsm format.

[Doofus1]

I needed some help - with the VB stuff.
IT WORKS!!!!
THANK YOU!!!

[Tony Valko]

I needed some help - with the VB stuff.

You and me both!

If you need specific help try posting your questions in the Excel Programming / VBA / Macros forum.

IT WORKS!!!!
THANK YOU!!!

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.