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. I want to prepare a list where any

name should appear only once and that too

with the best-of-two scores. Hence if Jane

scored 120 and 143 in her two tests, i want to

see only 143 against her name, effectively

deleting her name and her score of 120. In

short I want to then give ranks in descending

order of their scores. Can anyone pl help?

Thank you in advance... ajay

Hi, Follow this sequence:

Assuming names are in column A and scores in column B, with headings in row 1.1. Create a list of the unique names.

Select the column containing the names, select 'Filter' - 'Advanced Filter...'

In the Advanced filter dialog box select 'Unique records only' and 'Copy to another location'. Enter a single cell Address in the 'Copy to' box - I used D12. Find the highest score for each name

In the column next to the unique names, starting at E2 (row 1 contains column headings)

enter this formula:

=SUMPRODUCT(MAX(($A$2:$A$1001=D2)*(($B$2:$B$1001))))

Change the end of range $A$1001 and $B$1001 to match the end of your list of names)

~~Select cell C2~~Select cell E2 and drag the formula to extend it down alongside the list of unique names.You now have the highest score alongside each individual

3. Rank the individuals

In E2 enter this formula:

=RANK(E2,$E$2:$E$8)

Change the end of range $E$8 to match the end of the range containing the unique names.

Drag this formula down to extend it alongside the list of unique namesThis will give you the rank values

Regards

Thank you Humar so much. I thought the solution would be very

lengthy but you made it so simple. though i have some doubt in

understanding the last part "select cell C2.." part, i will actually

put in my worksheet and try..

many thanks again.

ajay

Hi, My mistake - select cell C2 should be select cell E2 ...

E2 contains the formula that finds the maximum value for the name in cell D2.

Select E2 and drag it down. When selected, E2 will have a small dot in the bottom-right corner of the cell border. When you move the cursor over this dot, the cursor changes to a solid cross (not the one with four arrow heads).

Click the left mouse button, hold it down and drag the cursor down as many rows as you have unique names.As a check, look at the formula in say cell E10 - it should be:

=SUMPRODUCT(MAX(($A$2:$A$1001=D10)*(($B$2:$B$1001))))

The reference is now to D10 (the unique name on row 10), but the references to the source names and scores are unchanged (the $ signs stop the ranges changing when the cell is dragged).I will edit my prior response.

Regards

Thank you Humar 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. :(

Obviously there is something different about the 2 entries. Trying putting a column next to your names and dragging =CLEAN(A2) down to the bottom.

Then do a Copy...Paste Special...Values.

If that doesn't work try =TRIM(A2)

If you really want to find out what is different, copy one instance of John White into a cell in Row 1, e.g. D1 and copy the other instance into E1. In F1, enter this formula and drag it down.

As soon as you see the "is different" result, you'll know where the 2 instances differ.

=IF(MID($D$1,ROW(),1)=MID($E$1,ROW(),1),"Character " & ROW() & " is OK.", "Character " & ROW() & " is different.")

Yes DerbyDad03, thanks for the TRIM formula. silly me, i didn't

realize there could be difference in the blank space at the end of

the names. Good lesson for me, never say i've checked

everything :) thanks.

Humar, what am i doing wrong?

=SUMPRODUCT(MAX(($A$2:$A$1001=D2)*(($B$2:$B$1001))

))

i have entries till row 33910. if i copy the above formula and

change the limit to a smaller value, say b666, it works but

with=SUMPRODUCT(MAX(($A$2:$A$33910=D2)*(($B$2:$B$3391

0))))i get the message #VALUE! .... i must be missing

something? thank you in advance.-ajay

I tested your formula to 33910 and beyond and it worked fine as long as there were numbersin Column B.As soon as I changed any single number to text, I got a #VALUE error.

Try this:

Select any empty cell and Copy (Ctrl-C) it.

Select Column B and do an Edit...Paste Special...Add.

Hopefully this will change any number that is formatted as text to become a number again.

Hi, Put these two formulas in any two empty cells:

=COUNT($B$2:$B$33910)

=COUNTA($B$2:$B$33910)The first one counts non-blank cells

The second one counts the number of cells containing numbers

As column B should only contain numbers, then these two formulas should return the same number (33909)

If they return different values, one or more cells does not contain a number.As you have so many cells to look in try using the Auto Filter on column B and looking in the drop-down list for anything that is not a number, select it and only that row will be shown. Make the correction and remove the Auto Filter.

Regards

Thank you DerbyDad03 and Humar for the wonderful help. Yes,

there were some entries there were text-like. corrected them, i

gotta run now, so will check everything tomorrow morning (in my

time zone it's already Monday 8:30pm :) ).thank you. that was fantastic.

ajay

Hi DerbyDad03 and Humar, thought i'd let you know that my job's done. it's all sorted

perfectly and people will soon pat my back for having done a

great job, while all along i know whom to actually thank :)thank you.

ajay

Hi, You're welcome, and thanks for letting us know it all worked.

Regards

Humar

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History