Find The Repeated Data On The Sheet With Count Of Repetitions

        As sample, data in Column A was used in this Excel VBA Template.

When the button is pressed,the repeated data in "Column A" are listed  into "Column G" and "Column H" with the count of repetitions.

Our codes :
Sub repeating_data()
Dim t As Object, sonsat As Long, liste(), j As Long
    sonsatir = Cells(Rows.Count, "A").End(xlUp).Row
    Range("F2:H" & Rows.Count).ClearContents
    liste = Range("A2:A" & sonsatir).Value
    Set t = CreateObject("scripting.dictionary")
    For j = 1 To UBound(liste)
        If Not t.exists(liste(j, 1)) Then
            t.Add liste(j, 1), 1
            t.Item(liste(j, 1)) = t.Item(liste(j, 1)) + 1
        End If
    Next j
    Application.ScreenUpdating = False
    Range("G2").Resize(t.Count, 2) = Application.Transpose(Array(t.keys, t.Items))
        Range("G2:H" & Rows.Count).Sort Range("H2"), xlDescending
     Call number_of_repetitions
    Application.ScreenUpdating = True
End Sub

No comments:

Post a Comment