Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Function to eliminate duplicate entries in columns (2000 - SP-3)

    Is there an Excel Function to eliminate duplicate entries within selected columns?
    An example is the following: (I would first sort by columns A and B, select columns A and B, run the function, if one exists and the 9 rows would become 7 rows as seen below)
    Before running Function:
    A B
    1 cat 1
    2 cat 3
    3 cow 1
    4 cow 1
    5 cow 3
    6 dog 1
    7 dog 1
    8 horse 1
    9 horse 2

    If a function exists, it would end up looking like this after executing the function:
    A B
    1 cat 1
    2 cat 3
    3 cow 1
    4 cow 3
    5 dog 1
    6 horse 1
    7 horse 2

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Function to eliminate duplicate entries in columns (2000 - SP-3)

    See <post#=277108>post 277108</post#>

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to eliminate duplicate entries in columns (2000 - SP-3)

    Does this do what you want:

    <pre>Public Sub DelDups()
    Dim I As Long
    With ActiveSheet.Range("B1")
    For I = ActiveSheet.Range("B65536").End(xlUp).Row - 1 To 1 Step -1
    If .Offset(I, 0).Value = .Offset(I - 1, 0).Value Then
    .Offset(I, 0).EntireRow.Delete
    End If
    Next I
    End With
    End Sub
    </pre>

    Legare Coleman

  4. #4
    Lounger
    Join Date
    May 2002
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to eliminate duplicate entries in columns (2000 - SP-3)

    Thanks for the info. It worked. I appreciate it!

  5. #5
    Lounger
    Join Date
    May 2002
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to eliminate duplicate entries in columns (2000 - SP-3)

    I used the advanced filter option but I am going to keep a copy of this macro. It may come in handy in the future. I appreciate it!

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Function to eliminate duplicate entries in columns (2000 - SP-3)

    One advantage of the Adv filter over the macro, is that the data does not have to be sorted for it to work.
    The disadvantage is the extra step to copy and paste

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to eliminate duplicate entries in columns (2000 - SP-3)

    Advanced Filter can be activated from within the destination sheet to eliminate copying and posting.

    Aladin
    Microsoft MVP - Excel

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Function to eliminate duplicate entries in columns (2000 - SP-3)

    It won't directly eliminate the duplicate entries. Even if you filter to a new location, you then must delete the original data. You can filter in place (which does not delete the unique entries, and you can not copy the unique entries over the original to get the new list.

    So if your goal is to eliminate the dupes from the list (which was the question), it can not be done directly with adv filter without some copy/paste. Legare's code would do this (if the list is sorted).

    Steve

Posting Permissions

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