Quickest way to gather unique values in column - preferably without looping

Hi guys,

I've got a sheet with just over 250,000 rows in which I filter multiple columns based on dropdown values in another sheet.
After all the filtering, I'd want to count the number of unique values in a filtered column - preferably without looping.
I can't use advanced filter / copy unique values either.

What's the quickest way?

Thanks,

Jasper

ps - right now I'm using:

Please Login or Register  to view this content.

I suspect a Dictionary would be faster than string concatenation.

Depending on big picture of what you are doing and your work flow, you may want to explore using ADO and SQL to count the unique values, retrieve data, etc. e.g. separate the front-end UI (i.e. what's on the other sheet where you do the filtering) and the quasi DB (i.e. the sheet with 250K+ records). This way you can work with it without even opening the huge file.
Further on you can use access or sqlite or even mysql (free) to store data in real DB.
just my two cents. In a pure VBA I'm using the same approach as suggested by dominicb.

Dictionary version:

Please Login or Register  to view this content.

you can google ADODB Excel and it will return plenty of results, even videos on youtube on how to use ADO from excel.
here is old thread where I've uploaded sample files for the other user.

@AB33: I said MySQL, no MS SQL Server. No experience with MS SQL Server installation, but MySQL installation went well on several occasions I had to install it myself

sorry, AB33, I misunderstood you :-)