+ Reply to Thread
Results 1 to 6 of 6

how to delete rows based on duplicate information in a column

  1. #1
    Registered User
    Join Date
    05-11-2004
    Posts
    1

    how to delete rows based on duplicate information in a column

    Is there a way (beside writing a macro) to delete if you have identical entries in a column and you want to keep only one copy of that row. For e.g.

    A B C
    1 2 3
    1 4 5

    Since there are Two 1's in A i only want to keep one copy of that row.

    Thanks in advace for help,

  2. #2
    Paul Lautman
    Guest

    Re: how to delete rows based on duplicate information in a column

    albertpinto wrote:
    > Is there a way (beside writing a macro) to delete if you have
    > identical entries in a column and you want to keep only one copy of
    > that row. For e.g.
    >
    > A B C
    > 1 2 3
    > 1 4 5
    >
    > Since there are Two 1's in A i only want to keep one copy of that row.
    >
    > Thanks in advace for help,


    Data->Advanced filter has an option for removing duplicates



  3. #3
    Dave Peterson
    Guest

    Re: how to delete rows based on duplicate information in a column

    With headers in Row 1, you can use Data|Filter|Advanced filter to show just the
    unique entries.

    Debra Dalgleish shows how:
    http://www.contextures.com/xladvfilter01.html#FilterUR

    Do this based on just column A.

    Then you can select your range (all columns)
    edit|copy
    and edit|paste to a new location (a new sheet?)

    Only the visible cells should be copied|Pasted.

    albertpinto wrote:
    >
    > Is there a way (beside writing a macro) to delete if you have identical
    > entries in a column and you want to keep only one copy of that row. For
    > e.g.
    >
    > A B C
    > 1 2 3
    > 1 4 5
    >
    > Since there are Two 1's in A i only want to keep one copy of that row.
    >
    > Thanks in advace for help,
    >
    > --
    > albertpinto
    > ------------------------------------------------------------------------
    > albertpinto's Profile: http://www.excelforum.com/member.php...fo&userid=9370
    > View this thread: http://www.excelforum.com/showthread...hreadid=546032


    --

    Dave Peterson

  4. #4

    Re: how to delete rows based on duplicate information in a column

    Try this freeware:
    http://www.aks-labs.com/support/phpB...topic.php?t=69
    This is not Excel plug-in, but works for duplicates finding


  5. #5
    JB
    Guest

    Re: how to delete rows based on duplicate information in a column

    We assume number sorted in column A:

    Range("A1").Select
    Do While ActiveCell <> ""
    If ActiveCell = ActiveCell.Offset(-1, 0) Then
    ActiveCell.EntireRow.Delete
    Else
    ActiveCell.Offset(1, 0).Select
    End If
    Loop

    Cordialy JB


  6. #6
    aresen
    Guest

    Re: how to delete rows based on duplicate information in a column

    The unique record implementation will yield the first occurrence of
    each value in the list. That's fine if it's the first you want.
    However, in some cases the last occurrence is more valid since data is
    usually added to the end of a list and is the most recent.
    Let's say the last row of data is 100. To extract the last occurrence,
    select cells D2 and enter the formula =MATCH(A2,A3:A$101,0). Copy that
    down through to cell D100. You will get numbers and #N/A. Now select
    column D and use Edit... Go To... Special... Select Formulas but
    deselect Errors then hit OK. Now Edit... Delete... Entire Row. Now you
    can delete the formulas left in column D. Conversely, if the formula is
    =MATCH(A2,A$1:A1,0) and then copied down, you'll be retaining the first
    instances.


+ 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