Find # of Unique Numbers in Large Table

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
322
Office Version
  1. 2019
Platform
  1. Windows
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?

1616214392253.png
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
322
Office Version
  1. 2019
Platform
  1. Windows
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).
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Tupe77

Board Regular
Joined
Nov 26, 2020
Messages
100
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?

VBA Code:
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

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Feb 26, 2007
Messages
6,456
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jan 30, 2021
Messages
322
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jan 30, 2021
Messages
322
Office Version
  1. 2019
Platform
  1. Windows
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?

VBA Code:
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

Active Member
Joined
Jan 30, 2021
Messages
322
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
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

Active Member
Joined
Jan 30, 2021
Messages
322
Office Version
  1. 2019
Platform
  1. Windows
Diddi, now I get about 1600-1900 new rows each day, by years end however, that number will be doubled.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
so like 20 columns x 2000 rows a day?
 

Forum statistics

Threads
1,140,923
Messages
5,703,178
Members
421,280
Latest member
Jaycee01

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