Find # of Unique Numbers in Large Table

[ExcelAtEverything]

Inside of a very large 300k row table, I have a column containing 300k cells (C2:C300000) each containing a 10-digit number. I'm wondering if there's a faster and less CPU-intensive method for counting the number of unique numbers in that column. I've tried several methods now, but each seems to slow my computer down more than the last. The most recent formula I tried from the web =SUMPRODUCT(1/COUNTIF(C2:C300000,C2:C300000)) pretty much slowed my computer to a dead stop. Is there a faster/more efficient way to retrieve that number?

 

[ExcelAtEverything]

Thx Tupe77! I do have access yes, and I do have some knowledge of VBA. However after pasting your code in I remembered that I used a mocked up pic to send as my example, including the "300k" number of records to analyze. I figured I'd just translate whatever the formula answer would be to match my exact locations in my table. After you sent this vba response, I realized that I don't know how translate it in VBA.

So my info is in column C, but it's encompassed in a table, and that table will expand in side daily (hence why I opted to insert into a table). The column header in the table is "Order ID". Ths is a multi-tabbed workbook, but the tab I am using for this situation is "Monthly Reports" (if that matters).

 

[Tupe77]

I made a few changes, but I had to make the assumption that your table is the first ListObject on that sheet. Probably it is and everything is working properly.
The result only comes in msgbox, should it be placed somewhere?

Sub Counting_Uniques2()
    Dim wsCu As Worksheet: Set wsCu = ActiveWorkbook.Sheets("Monthly Reports")
    Dim TableObj As ListObject
    Dim ColObj As ListColumns
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    Dim CountRNG As Range, Cell As Range

Set TableObj = wsCu.ListObjects(1) ' WorkSheets("Monthly Reports") first Listobject(right table I hope?)
Set ColObj = TableObj.ListColumns ' Table Columns
Set CountRNG = ColObj("Order ID").DataBodyRange ' Table Columns("Order ID") datarange

    For Each Cell In CountRNG
        If Not dict.Exists(Cell.Value) Then
            dict.Add Cell.Value, 0
        End If
    Next
    MsgBox "Unique_values: " & dict.Count ' Result is only msgbox at this moment
 End Sub

 

[diddi]

no, clean out means "assess the duplicates and mark as done". leave the data as is. when new data comes in check for dupes, and update. data is untouched the whole time

 

[alansidman]

A PQ solution.
Highlight your column of data. Bring into PQ Editor
Group on that column and select Count Unique
Close and Load to a new sheet.
Sum the new column.

You still have your original data and now you have a new sheet with the result data.

 

[ExcelAtEverything]

A PQ solution.
Highlight your column of data. Bring into PQ Editor
Group on that column and select Count Unique
Close and Load to a new sheet.
Sum the new column.

You still have your original data and now you have a new sheet with the result data.

Alan, when you say to "group on that column", are you saying to select "Group By"? If yes, then there is no option there for "count unique". The only option I see that is similar sounding to that is "Count Distinct Rows". But when I do that, I'm not understanding how it's counting. For instance there are 5 instances of a particular number that I know for a fact. Selecting "Count Distinct Rows" shows that there are only 4 instances of it.

 

[ExcelAtEverything]

I made a few changes, but I had to make the assumption that your table is the first ListObject on that sheet. Probably it is and everything is working properly.
The result only comes in msgbox, should it be placed somewhere?

Sub Counting_Uniques2()
    Dim wsCu As Worksheet: Set wsCu = ActiveWorkbook.Sheets("Monthly Reports")
    Dim TableObj As ListObject
    Dim ColObj As ListColumns
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    Dim CountRNG As Range, Cell As Range

Set TableObj = wsCu.ListObjects(1) ' WorkSheets("Monthly Reports") first Listobject(right table I hope?)
Set ColObj = TableObj.ListColumns ' Table Columns
Set CountRNG = ColObj("Order ID").DataBodyRange ' Table Columns("Order ID") datarange

    For Each Cell In CountRNG
        If Not dict.Exists(Cell.Value) Then
            dict.Add Cell.Value, 0
        End If
    Next
    MsgBox "Unique_values: " & dict.Count ' Result is only msgbox at this moment
End Sub

Tupe77, thank you for doing that for me! And that works 100%! Unfortunately it also takes a minute or two to run each time, virtually freezing Excel while while it does. And also even if it was faster, the message box doesn't work. Ideally I need to see each number one time (& only one time) in the column. For the numbers where there were multiples of that same number, then all the extras would not be shown.

 

[ExcelAtEverything]

no, clean out means "assess the duplicates and mark as done". leave the data as is. when new data comes in check for dupes, and update. data is untouched the whole time

Diddi, while I'm sure you know exactly what you're talking about, I'm just not sure how to implement what you're saying, or whether or not that would be faster and less taxing. My brain is a little fried right now.

 

[diddi]

ok. how many rows of new records do you get per day? and how many fields are there in a record. i will do a demo run for you

 

[ExcelAtEverything]

Diddi, now I get about 1600-1900 new rows each day, by years end however, that number will be doubled.

 

[diddi]

so like 20 columns x 2000 rows a day?