avoid duplication in entries

Microsoft / Microsoft excel 2002
July 11, 2010 at 00:29:24
Specs: Windows XP
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

See More: avoid duplication in entries

July 11, 2010 at 05:15:20

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 D1

2. 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:
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:
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 names

This will give you the rank values


Report •

July 11, 2010 at 06:17:58
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.

Report •

July 11, 2010 at 06:49:29

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


Report •

Related Solutions

July 11, 2010 at 19:48:19
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. :(

Report •

July 11, 2010 at 20:27:52
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.")

Report •

July 11, 2010 at 23:58:52
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.

Report •

July 12, 2010 at 00:14:30
Humar, what am i doing wrong?
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


i get the message #VALUE! .... i must be missing
something? thank you in advance.-ajay

Report •

July 12, 2010 at 03:25:11
I tested your formula to 33910 and beyond and it worked fine as long as there were numbers in 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.

Report •

July 12, 2010 at 04:20:32

Put these two formulas in any two empty cells:

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.


Report •

July 12, 2010 at 07:59:57
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.


Report •

July 12, 2010 at 21:16:51
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.


Report •

July 13, 2010 at 04:04:40

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



Report •

Ask Question