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)
Bookmarks