getting best of two scores of the same student

[ajayshahane]

Hi all,
i am new around here. I have what i think is a unique problem with excel.
In column A i have thousands of student names and column B i have scores of these students for a test. Most students in column A have appeared twice and have got different scores, and hence their name appears twice in column A (Mary White will be in cell A24 with her score 86 in cell B24 and again Mary White in cell A901 with her score 37 in cell B901).

I want to prepare a list where any name should appear only once and that too with the best-of-two scores. Hence if Mary scored 86 and 37 in her two tests, i want to see only 86 against her name, effectively deleting her name and her score of 37. In short I want to then give ranks in descending order of their scores. Can anyone pl help? I am using the age-old Excel 2002.
Thank you in advance... ajay

 

[Robert Mika]

No the way you want (as for you will need VBA code) but this may help.

1.Sort your names by "Unique names"(Advance Filter)
2.Use formula =MAX(IF($A$1:$A$6=A14,$B$1:$B$6)) to get MAX for each user
(confirm this formula with CTRL+SHIFT+ENTER)
3.Sort A-Z by column C -"Rank" in cell c14


Please see attached example:

http://rapidshare.com/files/406291169/rank.xls

 

[mikerickson]

This is a non-VBA solution.
The range E2:E11 holds the array formula
=INDEX($A:$A,IF(ROW($A$2:$A$11)=MATCH($A$2:$A$11,$A:$A,0),ROW($A$2:$A$11),9999),1)&""

The cell F2 holds the CSE formula =LARGE(($A$2:$A$11=E2)*($B$2:$B$11),1))
and G2 holds =LARGE(($A$2:$A$11=E2)*($B$2:$B$11),2))

These are all entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

Then F2:G2 is filled down to F2:G11

<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=85><b>A</b><td width=25><b>B</b><td width=25><b>C</b><td width=25><b>D</b><td width=85><b>E</b><td width=25><b>F</b><td width=25><b>G</b><td width=25><b>H</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF>Name <td align="left" bgcolor=#FFFFFF>Score<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>High<td align="left" bgcolor=#FFFFFF>no.2<td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="left" bgcolor=#FFFFFF>Mary White<td align="right" bgcolor=#FFFFFF>21<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#CCFFCC>Mary White<td align="right" bgcolor=#FFFFFF>71<td align="right" bgcolor=#FFFFFF>51<td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="left" bgcolor=#FFFFFF>Bob Jones<td align="right" bgcolor=#FFFFFF>32<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#CCFFCC>Bob Jones<td align="right" bgcolor=#FFFFFF>82<td align="right" bgcolor=#FFFFFF>62<td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>4</b><td align="left" bgcolor=#FFFFFF>Sally Smith<td align="right" bgcolor=#FFFFFF>93<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#CCFFCC>Sally Smith<td align="right" bgcolor=#FFFFFF>93<td align="right" bgcolor=#FFFFFF>63<td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>5</b><td align="left" bgcolor=#FFFFFF>Ben Hur<td align="right" bgcolor=#FFFFFF>24<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#CCFFCC>Ben Hur<td align="right" bgcolor=#FFFFFF>24<td align="right" bgcolor=#FFFFFF>0<td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>6</b><td align="left" bgcolor=#FFFFFF>Mary White<td align="right" bgcolor=#FFFFFF>71<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#CCFFCC><td align="right" bgcolor=#FFFFFF>0<td align="right" bgcolor=#FFFFFF>0<td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>7</b><td align="left" bgcolor=#FFFFFF>Bob Jones<td align="right" bgcolor=#FFFFFF>62<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#CCFFCC><td align="right" bgcolor=#FFFFFF>0<td align="right" bgcolor=#FFFFFF>0<td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>8</b><td align="left" bgcolor=#FFFFFF>Sally Smith<td align="right" bgcolor=#FFFFFF>63<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#CCFFCC><td align="right" bgcolor=#FFFFFF>0<td align="right" bgcolor=#FFFFFF>0<td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>9</b><td align="left" bgcolor=#FFFFFF>Mary White<td align="right" bgcolor=#FFFFFF>51<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#CCFFCC><td align="right" bgcolor=#FFFFFF>0<td align="right" bgcolor=#FFFFFF>0<td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>10</b><td align="left" bgcolor=#FFFFFF>Bob Jones<td align="right" bgcolor=#FFFFFF>82<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#CCFFCC><td align="right" bgcolor=#FFFFFF>0<td align="right" bgcolor=#FFFFFF>0<td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>11</b><td align="left" bgcolor=#FFFFFF>Sally Smith<td align="right" bgcolor=#FFFFFF>33<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#CCFFCC><td align="right" bgcolor=#FFFFFF>0<td align="right" bgcolor=#FFFFFF>0<td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>12</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>13</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
</table>

 

[ajayshahane]

Many many thanks robert mika and mikerickson for the very quick solution. since i am not very good at excel, i will have to dig out what is exactly meant by ctrl+shift+enter, but i suppose that would be no big deal.

thank you again.
ajay

 

[Robert Mika]

Normaly you are confirming every formula with Enter.
In this case when you write or copy any of provided formula into your cell, hold the Control and Shift buttons and hit Enter key.
You will see that Excel surronds your formula with curly brackets so it will look like this:
={MAX(IF($A$1:$A$6=A14,$B$1:$B$6))}

Lokk here
http://www.ozgrid.com/Excel/arrays.htm

for more about arrays formulas.

 

[ajayshahane]

Thank you Robert Mika for being generous with your time and efforts. I will now try it.

Though not really related, i have a new problem. I copy-pasted this excel database from a pdf file. I have found that in some cases Excel treats two entries of Mary Smith as duplication and does the trick, while in others - say John White - it treats them as different entries. I double checked - there were no spelling or space errors; in fact if i copy paste the name John Smith from its first location to its second, then Excel does not have problems. That means i could not fully trust the Auto Filter.