Duplicate entries
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Duplicate entries

  1. #1
    Guest

    Default

     
    I have a spreadsheet with approximately 55000 lines in it, and 12 columns. I would like to know if anybody knows how to (in one column) pick up duplicate entries so that I can then delete the duplicated entries, at the moment I have to go through each line indivdually and look and check to see if it is duplicated and then sort and delete, I would really like to pick up the duplicate entries, and then be able to delete them all in one go if possible.

    Thank You John Harcourt-Rigg
    Brisbane Australia

    rosella@bigpond.net.au

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Depends how you define duplicates...is the whole row the same or just the cells in a particular column?

    Anyway, the quickest non-VBA way I can think of doing this is to use Advanced Filter. Select your range, choose Data, Advanced Filter and check Unique Records only. When this is done select the whole of your range, hit F5, choose Special and then Visible Cells Only. Copy and then paste to a new sheet - et voila!

    HTH,
    D

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Here's another way

    If your data is in Column A, starting at A2
    Put this formula in B2 and scroll down.
    This will return "duplicate" against all duplicate entries. Filter Column B to show only "duplicate" and delete the rows.

    =IF(ISNA(VLOOKUP(A2,$A$1:A1,1,FALSE))=TRUE,"","duplicate")

    Good Luck
    Derek

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,872
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-02-26 04:45, Anonymous wrote:
    I have a spreadsheet with approximately 55000 lines in it, and 12 columns. I would like to know if anybody knows how to (in one column) pick up duplicate entries so that I can then delete the duplicated entries, at the moment I have to go through each line indivdually and look and check to see if it is duplicated and then sort and delete, I would really like to pick up the duplicate entries, and then be able to delete them all in one go if possible.

    Thank You John Harcourt-Rigg
    Brisbane Australia

    rosella@bigpond.net.au
    As Derek also noted, it's not clear what you take to be a duplicate. What follows takes up two obvious possibilities.

    [1]

    Lets say that A4:C8 houses the following sample data:

    {"field1","field2","field3";
    "a1",2,3;
    "a2",6,8;
    "a1",7,6;
    "a1",2,3}

    If you want to keep one record of all the records that are identical like row 5 and row 8:

    Activate A5;
    Activate Data|Advanced Filter;
    Set 'List range' to (if not already set) $A$4:$C$8;
    Check 'Copy to another location';
    Set 'Copy to' e.g., $H$4;
    Check 'Unique records only';
    Click OK.

    [2]

    If you want to keep just one record that has A-value (that is, a single record headed with "a1" and "a2", regarding the sample above:

    In D4 enter: field4 [ just a label ]

    In D5 enter: =ISNUMBER(MATCH(A5,$A$4:A4,0))+0 [ copy down this till last row of data ]

    In A1 enter: field5 [ just a label ]

    In A2 enter: =D5=0

    Activate A5;
    Activate Data|Filter|Advanced Filter;
    Set 'List range' to (if not already set) $A$4:$D$8;
    Check 'Copy to another location';
    Set 'Copy to' e.g., $L$4;
    Click OK.

    You can copy the range where the data are filtered to and paste the copy in a different worksheet. Delete the old worksheet if desired.

    Note. Make a copy of your WB before trying out these recipes.


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com