Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Sorting Duplicates

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    I need an easy way to sort and remove duplicate entries (ie: names) from a list.

    So far I am sorting, then manually scrolling and looking for the dupes, but as my list is 10,000 names, it is getting too time consuming....

    Can anyone help?

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This macro will do what you want.

    I don't remember who I got this from, so if it's yours, please take credit.

    This macro will delete duplicate rows in a range. To use, select a single-column range of cells, comprising the range of rows from which duplicates are to be deleted, e.g., C2:C99. To determine whether a row has duplicates, the values in the selected column are compared. Entire rows are not compared against one another. Only the selected column is used for comparison. When duplicate values are found in the active column, the first row remains, and all subsequent rows are deleted.

    Public Sub DeleteDuplicateRows()
    '
    ' This macro deletes duplicate rows in the selection.
    ' Duplicates are counted in the COLUMN of the active cell.

    Dim Col As Integer
    Dim r As Long
    Dim C As Range
    Dim N As Long
    Dim V As Variant
    Dim Rng As Range

    On Error GoTo EndMacro
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Col = ActiveCell.Column

    If Selection.Rows.Count > 1 Then
    Set Rng = Selection
    Else
    Set Rng = ActiveSheet.UsedRange.Rows
    End If

    N = 0
    For r = Rng.Rows.Count To 1 Step -1
    V = Rng.Cells(r, 1).Value
    If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
    Rng.Rows(r).EntireRow.Delete
    N = N + 1
    End If
    Next r

    EndMacro:

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    End Sub


    [ This Message was edited by: lenze on 2002-05-03 13:19 ]

  3. #3
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  4. #4
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    hello there
    take a look at this url

    http://www.cpearson.com/excel/deleting.htm

    I think Chip Pearson's macro is the best choice for you

    Regards

    Andreas

  5. #5
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Actually, I think the one I gave came from either Chip or Dave Hawley

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If A1:A7 contains...

    {"Names"
    ;"John Lennon"
    ;"Paul McCartney"
    ;"Ringo Starr"
    ;"John Lennon"
    ;"Ringo Starr"
    ;"George Harrison"}

    ...and was named, 'List'. Using the Get External Data menu command, the Excel ODBC driver and the following SQL....

    SELECT DISTINCT List.Names
    FROM List List
    ORDER BY List.Names

    ...you'd get...

    {"Names"
    ;"George Harrison"
    ;"John Lennon"
    ;"Paul McCartney"
    ;"Ringo Starr"}



    [ This Message was edited by: Mark W. on 2002-05-03 14:51 ]

Some videos you may like

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
  •