# 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

#1
July 11, 2010 at 05:15:20
 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 nameIn 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 individual3. Rank the individualsIn 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 valuesRegards

Report •

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

Report •

#3
July 11, 2010 at 06:49:29
 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

Report •

Related Solutions

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

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

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

#7
July 12, 2010 at 00:14:30
 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\$33910))))i get the message #VALUE! .... i must be missing something? thank you in advance.-ajay

Report •

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

#9
July 12, 2010 at 04:20:32
 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 cellsThe second one counts the number of cells containing numbersAs 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

Report •

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

Report •

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

Report •

#12
July 13, 2010 at 04:04:40
 Hi,You're welcome, and thanks for letting us know it all worked.RegardsHumar

Report •