# Thread: One Column Confusion! Thanks: 0 Likes:  1 Post #5341099 (1)

1. ## One Column Confusion!

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

2. ## Re: One Column Confusion!

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:

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.

3. ## Re: One Column Confusion!

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

4. ## Re: One Column Confusion!

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!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•