One Column Confusion!

[JScotty317]

Greetings!

I have a single column with 1,013,922 rows of values. There are 585,636 unique values in this column. I'm trying to determine how many times each of those 585,636 values occur in this column without entering a variation of the "COUNTIF(A:A,B2)" formula on each line of the 585k values. I've gotten about 11k rows calculated in over a little over 3 hours and there HAS to be a more efficient way. My computer just can't compute more than 150 rows at a time without freezing.

Can you guys help? Thanks!

- Justin

 

[Eric W]

You could try a macro.

Open a copy of your workbook. Open to the sheet with the data. Press Alt-F11 to open the VBA editor. Press Alt-IM to insert a module. Paste this code in the window that opens:

Sub test1()
Dim MyData As Variant, Dict As Object, i As Long

    MyData = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    Set Dict = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(MyData)
        Dict(MyData(i, 1)) = Dict(MyData(i, 1)) + 1
    Next i
    For i = 1 To UBound(MyData)
        MyData(i, 1) = Dict(MyData(i, 1))
    Next i
    
    Range("B1").Resize(UBound(MyData)).Value = MyData

End Sub

Make sure the ranges (red) are what you want. This reads column A, and puts the results in column B. Press Alt-Q to close the editor. Press Alt-F8, choose test1, click Run. This ran in under a minute on my PC, with over a million rows.

 

[petertenthije]

Would a pivot table not be the fastest way to do so?

 

[JScotty317]

Thank you both so much! My workplace PC is locked down like crazy otherwise I'd just send it to myself at home to compute. The macro absolutely solved my issue, thank you much!

 

[Eric W]

Glad we could help.