Results 1 to 5 of 5

Thread: One Column Confusion!

  1. #1
    New Member
    Join Date
    Jan 2016
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default 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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    Board Regular
    Join Date
    Sep 2012
    Posts
    129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: One Column Confusion!

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

  4. #4
    New Member
    Join Date
    Jan 2016
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!

  5. #5
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: One Column Confusion!

    Glad we could help.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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