Count unique values real-time, need a faster way

Cannot upload a spreadsheet at the moment unfortunately but I will try to explain clearly

I have a template sheet which usually has 50k to 75k rows, and about 70 columns pasted into it.

What the template does is generate spreadsheets based on each unique value in a selected column.

The template file itself will be used for a variety of applications, so the column the file is to be split by can have variable data in it.

I created a simple form to simplify the process of selecting a column. It contains a large listbox which populates with the Headers pasted into the template;

Please Login or Register  to view this content.

Nothing too exciting there...

On the bottom, I have a label (label3) which is to indicate the total number of spreadsheets that will be created. This is based on the number of unique values in the selected column. My initial attempt was to use a helper sheet (TEMP) with some pre-set formulas which listed the header columns from the DUMP sheet, and a formula to count unique values in the DUMP sheet, and use a worksheetfunction.vlookup to extract the value

Please Login or Register  to view this content.

While this method works fine in testing with a couple dozen rows, having 80,000 rows and 100 columns makes things run awful slow.

I attempted a VBA solution to count the uniques per column but it was equally as slow....I forget where that code was from though :\

Any ideas? Let me know if more information is needed.

You could try copying the column of values into a new sheet and use the remove duplicates functionality to get to a unique set of values. You can then use this to populate you list. To get the syntax, try recording a macro which does the above manually.

I've used this approach successfully on datasets of a similar size.

I'll give it a shot - it would need to repeat each time a different column is selected from the listbox though.

Another thought I had right after I posted was to have a pivot table somewhere that dynamically changed row headers with a count for values as I clicked, since pivots seem to count unique very efficiently