Extracting unique values and counting them in large xls

[nn992]

Hello everyone,


I have an excel file like table below. I want him to extract me only the unique items from "Source" column and then to count them in "Ocurences" column.

I did it with with DATA--->Advanced--->copy unique entries etc.
and then countif...
but this xls has 8000+ rows, so it crashes when I try to do it in this way... Any macros or suggestions how to do it?

 

[stumac]

A pivot table would be one really quick way to achieve this. Use the Source as the row values and then a count of them in the total.

 

[Fluff]

How about

Sub GetDupes_Qty()

    Dim Cl As Range

    With CreateObject("scripting.dictionary")
        For Each Cl In Range("G2", Range("G" & Rows.Count).End(xlUp))
            If Not .exists(Cl.Value) Then
                .Add Cl.Value, 1
            Else
                .Item(Cl.Value) = .Item(Cl.Value) + 1
            End If
        Next Cl
        Range("H2").Resize(.Count).Value = Application.Transpose(.keys)
        Range("I2").Resize(.Count).Value = Application.Transpose(.items)
    End With
        
End Sub