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
 

cadafi

New Member
Joined
Mar 29, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
@cadafi the UNIQUE function is only available in the 365 version of office, not 2016 or 2019.
And as the OP is using 2019 it's not much help.
@Fluff, Sorry I forgot it.
So there are some alternative approach that you can use Pivot-Table, or advance filter to extract the unique list then count it, in stead of using VBA or Array formula.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
322
Office Version
  1. 2019
Platform
  1. Windows
@Fluff, Sorry I forgot it.
So there are some alternative approach that you can use Pivot-Table, or advance filter to extract the unique list then count it, in stead of using VBA or Array formula.
Thanks Cadafi! Pivot Tables are the route I chose to go. In a separate thread topic where I was looking for options for my data-heavy CPU-stressed sheet, RoryA suggested to me that I could create a connection only query and use that as the data source for pivot tables, & that I could then load it to the data model and use Power Pivot. So that is exactly what I did. Although I'm still getting thru it (as I'm still a newb & only a couple of weeks old on both Power Query & Pivot Tables), switching to PT's using a data model eliminated any lags in my sheet, and it is lightning fast now. Pivot Tables definitely a much better option for large data sets like what I have here.
--> Link to that thread if anyone is interested: (Reaching Excel's Limits)
 
Solution

Forum statistics

Threads
1,140,921
Messages
5,703,175
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