+ Reply to Thread
Results 1 to 10 of 10

Removing Duplicates based on column values and criteria

  1. #1
    Registered User
    Join Date
    06-13-2012
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    5

    Removing Duplicates based on column values and criteria

    Hi guys I need some help removing duplicate names. Students were allowed to take a quiz as many times as they wanted. I need to remove the duplicate entry by keeping the highest grade.

    Here is the setup of my excel file. Column 1 has surnames, Column 2, has first name, and column 3 has grade.

    I can't figure out how to filter them based on first and last name because some students have the same name. with the grade as the criteria

    I would need an excel formula not macro

    Thanks guys
    Attached Files Attached Files
    Last edited by viduran88; 04-04-2013 at 11:38 AM. Reason: attaching sample workbook

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Removing Duplicates based on column values and criteria

    Use a fourth column to join the two names together (i.e. =A2&B2), then you should have unique names.

    Hope this helps.

    Pete

  3. #3
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Removing Duplicates based on column values and criteria

    Agree. Code can be A2&" "&B2. With a space, name can looks nicer

  4. #4
    Registered User
    Join Date
    06-13-2012
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Removing Duplicates based on column values and criteria

    Quote Originally Posted by wenqq3 View Post
    Agree. Code can be A2&" "&B2. With a space, name can looks nicer
    thanks for the help, but how would I filter them based to leave the highest grade. I have been trying with pivot table but I can't get it quite right, been trying with a dmax formula as well. The real trouble I am having is trying to figure out the criteria to use.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Removing Duplicates based on column values and criteria

    Well, you will get more detailed help if you posted a sample workbook, and then we can see more easily what you are trying to achieve. The FAQ describes how to attach a workbook to a post.

    Pete

  6. #6
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Removing Duplicates based on column values and criteria

    Filter with customs: set your criteria higher or equal than

  7. #7
    Registered User
    Join Date
    06-13-2012
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Removing Duplicates based on column values and criteria

    I have attached the sample workbook

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Removing Duplicates based on column values and criteria

    The attached file shows the table in the yellow cells. There are also formulae in helper columns E to G, and you may need to adjust the ranges to suit your real data - note that the formula in column F is an array formula, so after amending this you need to commit the new formula using the key combination of Ctrl-Shift-Enter rather than the usual <Enter>.

    Hope this helps.

    Pete
    Attached Files Attached Files

  9. #9
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Removing Duplicates based on column values and criteria

    Try this:-
    Please Login or Register  to view this content.
    Regards Mick

  10. #10
    Registered User
    Join Date
    06-13-2012
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Removing Duplicates based on column values and criteria

    Thanks for the help guys I ended going with pete_uk formuale but the =IF(C2=MAX(IF(E$2:E$10=E2,C$2:C$10)),MAX(F$1:F1)+1,"-") to =IF(C2=MAX(IF(E$2:E$10=E2,C$2:C$10)),"FINAL","-") then filtered by the word FINAL

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1