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]

Yep! 21 columns to be exact.

 

[diddi]

well i have had a small think about it and here is the start of a solution...
i have worked up a sub that can do this on a single column of data, and it is not hard to expand to cover your 21 columns:

so now if the count is performed on your current data and then saved on the sheet somewhere, when you add the new day import, you can run this code on 20,000 items as follows:

this gives you a new amount to add on to the existing total. most users will excuse your .184 second wait to get something done that used to hang their machine and these times include dumping the unique results to a sheet again.

do you see the direction i am trying to head

 

[diddi]

the other issue i see is that you need to consider a redesign of the whole project architecture to take into account the huge amount of data. it may be possible to summarize sections and archive it on the fly to keep the file size down and the speed up. depends on the application which you have not stated at this stage. there are often many ways to consolidate data so it is still accessible, but not compromising usability.

 

[ExcelAtEverything]

Diddi, That looks great... but I still don't think that the VB option will work. The reason is that on a regular basis, it will need to be run on just part of the list rather than the full list, a list based o date and/or store location. Simply having a raw number of the dead count of the complete list wont help me with that. Perhaps if I was VB Jedi status like yourself, but unfortunately I am a novice in VB.

Also, I totally agree with you about the data. Unfortunately I wasn't aware of this until I got into it pretty deep. I was told how easy the project would be and how it's just "pulling info from the Exce sheets we already get, and plugging it into this new sheet so it all looks pretty and readily available". The person who told me that however has no clue how things like this work. I don't claim to be a master by any stretch, but he asked me to do this based on a few sheets I did at a prior organization which worked perfectly and very impressively. But it was completely different, as the data was far closer to the end product. This company is fairly new, and their reporting sucks. So the Excel sheet needs to do the bulk of the work, which is what is killing my data capacity. I'm actually pretty sick about it, because what I thought was going to be an amazing looking and working document, is turning into a nightmare which is fast approaching the limits of processing.

 

[diddi]

i dont mind providing assistance to get the project moving in the right direction if you would like. adding the option to calculate counts on parts of the list is relatively easy. and even if it take 2 or 3 seconds to do it is way less than the 'not responding' times you are having now...
as for "mission creep" i have had long term projects for small businesses which have had complete rewrites because what the business asked for and what they discovered they could have once they saw the possibilities was so vastly different, all the data structures were wrong and the first iteration was unworkable. the other thing is that end users rarely have any understanding of what is going on 'behind the screen' so for an example, what is a simple after thought like adding an extra column to do "x" completely screws all the VBA that is cell referenced and takes hours to double check, but 'you only had to insert a column'

 

[ExcelAtEverything]

Oh, that's good to know! 2-3 seconds is nothing! Also thanks so much Diddi, you and I are eye to eye right now on that! Lol! It's so aggravating! I keep hearing his words echoing in my head "this'll be easy money for you bro! Just moving a little info from here to there, nothing major". I'm in another recent post of mine right now which also led to discussing reworking the sheet w/ RoryA, Michael M, and Offthelip. I need to resolve "mission creep" (hilarious) before I go any further. So I'm going the try looking into Pivot Tables tonight, which RoryA said I can just access access a folder which will contain the data files without the need of even bringing them into Excel. If not, Michael M and Offthelip had two great suggestions as well which seem similar to what you were getting at.

 

[diddi]

vg. follow up if you feel the need

 

[ExcelAtEverything]

Much appreciated Diddi.

 

[cadafi]

If you are using Excel 2016 or latest, you can use the formula: =COUNT(UNIQUE(C2:C300000))

 

[Fluff]

@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.