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?

 

[diddi]

if you only need to check now and then, rather than have the exact number available constantly, you can use the remove duplicates tool in the data menu. that gives you a row count (hence # of uniques). then just undo or dont save.

 

[ExcelAtEverything]

Thanks Diddi, but unfortunately that won't work. This sheet wil be updated daily each morning with new additional info, and will need that refreshed number each time for accuracy. I also don't wnt to put that on the end user of this sheet to have to do that each time.

Any other ideas?

 

[diddi]

yes... another possible method is to hide alist of unique values on a spare sheet (hidden?)
initially set up the sheet using the method i suggested above. then in your code, when the user adds a new row, check your list to see if it is new. that will require significantly less grunt than your current method. use last used row method to obtain the count. if you want a smarter method then i can devise faster ones by indexing the list but if this is the right idea then let me know if you need help to implement it

 

[ExcelAtEverything]

Hmmm. I feel like that option is wrong for this. When the new data is loaded each day (via Power Query), there will be nothing but brand new numbers... none of which could possibly already be in the list. But some in the group of new numbers will be duplicates of themselves. One additional note too... I would like to avoid creating an additional couple hundred thousand rows of data to accomplish this if at all possible. Between all the different tabs in this workbook, it is extremely large already. By years end there will likely be roughly 900k-ish rows of data between all sheets in the WB.

What do you think?

 

[I_cant_read]

You could try exploring Power Query. This is something in excel which is enabling users to easily process large data sets.

Cheers

 

[diddi]

so clean out the duplicates once, then on import, clean out new duplicates and then update a counter

 

[ExcelAtEverything]

I stated that 'm already using PQ, 'I_cant_read'. And every thing I find online about how to do this only allows me to get rid of the duplicates, which I don't want to do because there is info in other columns that is still needed. SO I cannot delete duplicates, I rather just need to count hoiw many of those numbers are unique (because this will provide me with an accurate number of transactions.

 

[ExcelAtEverything]

When you say 'clean them out", are you saying to delete them? Wouldn't that delete the entire row of info?

 

[Tupe77]

Is it possible to use vba?
If so, does this produce the desired result?

Sub Counting_Uniques()
Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    Dim CountRNG As Range, Cell As Range
    Set CountRNG = ActiveWorkbook.Sheets("TEST").Range("C2:C300000") ' Range to count uniques
    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
 End Sub