One Column Confusion!

JScotty317

New Member
Joined
Jan 11, 2016
Messages
7
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,633
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:

Rich (BB code):
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.
 

JScotty317

New Member
Joined
Jan 11, 2016
Messages
7
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,017
Messages
5,599,359
Members
414,306
Latest member
Dennis_vdw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top