Results 1 to 10 of 10

Remove duplicates and count their number - faster method needed

Threaded View

  1. #1
    Registered User
    Join Date
    04-18-2011
    Location
    Bucharest, Romania
    MS-Off Ver
    Excel 2019
    Posts
    90

    Remove duplicates and count their number - faster method needed

    Morning to all :>

    I need to be able to remove all duplicates from a sheet, keep only 1 record of those duplicates and know how many duplicates there we're in total for each duplicate found. I can already do all this, but my method is painfully slow.

    I attached a file so that u can get the idea better.

    I use 2 macros

    One is to sort my data in ascending order for columns "A:B" (done it with record macro)

    Sub sortare1()
    
        ActiveWorkbook.Worksheets(1).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(1).Sort.SortFields.Add Key:=Range("A:A") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets(1).Sort.SortFields.Add Key:=Range("B:B") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets(1).Sort
            .SetRange Range("A:B")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
    End Sub
    and the main macro witch does all the work

    Sub RemoveDuplicates1()
    
        Sheets(1).Select
      sortare1
        totalrows = ActiveSheet.UsedRange.Rows.Count
        Count = 1
        For Row = totalrows To 2 Step -1
            If Cells(Row, 1).Value = Cells(Row - 1, 1).Value And Cells(Row, 2).Value = Cells(Row - 1, 2).Value Then
                Rows(Row).Delete
                Count = Count + 1
            Else
                Cells(Row, 3).Value = Count
                Count = 1
            End If
        Next Row
        Cells(1, 3).Value = Count
        Worksheets(2).Range("C1") = "Nr. aparitii"
    End Sub
    As I said the macro works fine, but it sooooo very slow.

    Sheet 1 contains the raw data and in Sheet 2 I've run the macro so u can see an example. (As a bonus maybe u can tell me why "C270" is there)
    Attached Files Attached Files
    Last edited by ciprian; 05-02-2011 at 07:43 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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