CODEWITHSUNDEEP
excelvba
Sebastian Ong
Sebastian Ong

Reputation: 65

Getting the 10 Top most frequent names in a column

I have been struggling to come up with a program capable of extracting the 10 most frequent names appearing in a column and storing them into an array for further use.

Upvotes: 1

Views: 823

Answers (1)

user4039065
user4039065

Reputation:

Gather the column's values into an array to speed processing. Transfer to a dictionary's keys with the frequency as each key's item. The worksheet's Large can easily find the 10th largest frequency. Remove anything that has a lower frequency.

Option Explicit

Sub gfdrew()
    Dim i As Long, j As Long, arr As Variant, k As Variant, dict As Object

    Set dict = CreateObject("scripting.dictionary")

    With Worksheets("sheet6")
        arr = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)).Value2
    End With

    For i = LBound(arr, 1) To UBound(arr, 1)
        dict.Item(arr(i, 1)) = dict.Item(arr(i, 1)) + 1
    Next i

    j = Application.Large(dict.items, Application.Min(10, dict.Count))

    For Each k In dict.keys
        If dict.Item(k) < j Then dict.Remove (k)
    Next k

    arr = dict.keys

    For i = LBound(arr) To UBound(arr)
        Debug.Print arr(i)
    Next i
End Sub

Upvotes: 3

Related Questions