+ Reply to Thread
Results 1 to 13 of 13

Finding Duplicates

Hybrid View

  1. #1
    Registered User
    Join Date
    05-13-2009
    Location
    melrose, nb
    MS-Off Ver
    Excel 2003
    Posts
    32

    Finding Duplicates

    Just wondering how I can make Excel find all duplicate records in a column? For example, if 2079999-A occurs 3 times in the same column, I would like the 2nd and 3rd ones hilighted so I can erase them. At the end of the day I just want no duplicates.

    I could manually do it but the records are in the thousands and I'm hoping there's an easier way?

    Thanks!

  2. #2
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Finding Duplicates

    if your values are in column A, B1
    =countif($A1:A1,A1)>1
    evalutes to TRUE for all but the first instance of each entry in the column, copy this formula down alongside your data, then filter it for 'true' then delete the records

    An alternative approach is to use data -> advanced filter and select 'unique records only' - this only filters the data (doesn't delete)

    hth
    CC


    If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.

  3. #3
    Registered User
    Join Date
    05-13-2009
    Location
    melrose, nb
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Finding Duplicates

    Hi All,

    I'm trying to make this work but with no luck:

    B1
    =countif($A1:A1,A1)>1
    evalutes to TRUE for all but the first instance of each entry in the column, copy this formula down alongside your data, then filter it for 'true' then delete the records

    Any idea what would prevent it from working? It is returning all values as FALSE though I know some should be TRUE.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Finding Duplicates

    then they are not exact matches find 2 you think are the same and test them eg =A2=A4
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Finding Duplicates

    Hi

    Maybe...

    =COUNTIF(A:A;A1)>1

    Copy down.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  6. #6
    Registered User
    Join Date
    05-13-2009
    Location
    melrose, nb
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Finding Duplicates

    Does it matter that some of the numbers are 8 digits, some are 8 with a hyphen and another number etc.?

    I tried changing it to a semicolon instead of comma but it didn't like that. Tried it again with a comma and it returned some "true" values this time but not the correct ones. Other thing I am wondering is if maybe some of the cells aren't formatted right? I notice a few of them have the "comment" icon and it says "this number is formatted as text or proceeded by an apostrophe". Is there an easy way to make sure those are all correct and eliminate that as a possible problem?

  7. #7
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Finding Duplicates

    Hi

    I think, it's time to upload a sample workbook!

  8. #8
    Registered User
    Join Date
    05-13-2009
    Location
    melrose, nb
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Finding Duplicates

    R2_Sampled_Points_Feb_17_2012.xls
    Quote Originally Posted by Fotis1991 View Post
    Hi

    I think, it's time to upload a sample workbook!
    Attached is the spreadsheet. Basically I am trying to run the formula in column C on the data in column B. This info all came directly from GPS units, so I didn't do any formatting of cells or any such stuff.

    Not sure if relevant but I guess I should add it is off of Garmin GPS units, saved to ArcMap shapefiles (which creates a .dbf file, which can be saved as an Excel file).
    Last edited by eyeball11; 02-22-2012 at 02:41 PM.

  9. #9
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Finding Duplicates

    Hi

    Something like this, is what are you looking for?
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-23-2012
    Location
    Illinois
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Finding Duplicates

    Hi,

    I had a go with your attachements and found you had to remove the $ from the formular or else it did not allow for the first instance as being unique. Other than that it worked fine and solved an issue that bugged me for ages.

    Thanks very much Fotis!

  11. #11
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Finding Duplicates

    its still hard to tell perhaps something like this removes spaces and any thing with -XX
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    02-22-2012
    Location
    Telford UK
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Finding Duplicates

    Hi, I work daily with this problem, the way I have resolved it is as follows,

    Your Data in column A, insert new column at B, Sort A a-z acending. in B2 insert formula Exact B2 Exact as B1 copy down whole completed column, then filter "True" (All true's are duplicates, the first duplicate is ignored. These true's can be deleted. == No duplicates

    When you've Done this a couple of times , takes seconds !!

  13. #13
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Finding Duplicates

    are you sure? to correct fotis you need =IF(COUNTIF($B$2:B2,B2)>1,"Doumple","ok"),
    why didn't you just use the remove duplicates option on the data tab?

+ 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