VBA to show the count of unique records

Hi
I have a excel file were i'm using more than 25000 cells and most of the records in it will be duplicate records. In order to get the unique records im using the "Remove duplicate option" but were as to make my summary report im using a formula to count the unique records thus increasing my file size gradually since im going to use it for more records.

So i wanted to check the possibility to do this using vba to preserve my file size.

It may not be very clear with my explanation, so i have attached the sample file.

Thanks

if you want to count the unique records only then single cell solution is
copy paste below in C2 then hold control and shift then hit enter to make it array formula
=SUM(IF(FREQUENCY(MATCH(A2:A75,A2:A75,0),MATCH(A2:A75,A2:A75,0))>=1,1,0))

change the references as per of your actual data

if you want to have unique records preserving your actual data then select your data with headers then go to data tab---> Sort and Filter -----> Select Advance.
select copy to another location (enter the cell where you want your unique data to start) hit Ok.

Moreover if you want to do it through VBA code, try this code......

Please Login or Register  to view this content.

@hemesh

Good one hemesh.

Hello muralidaran,

Here is the fastest method to use in VBA for finding unique values. On my computer it would take about 1.3 seconds to scan 25000 rows and return the unique records.

The attached workbook has the macro below added to it and a button on the worksheet to run it. All previous values in the "Uniques" column are cleared each time the macro is run.

Please Login or Register  to view this content.

@ Leith Ross,

Hello Leith,

What an excellent piece of Coding!

Just to ensure D1 does not lose its heading, I would tweak your Code as follow.

Please Login or Register  to view this content.

Kind Regards.

Hello Winon,

Thanks for the tweak. I have included it in the updated macro and workbook.

Please Login or Register  to view this content.

Thanks everyone.....
Mr. Leith Ross your code works fine but my requirement is to show the running count in column B, could you please help me to provide VBA code to show the running total in column B.

Thanks.

Hi muralidaran,

I'll let Leith provide an answer on his fantastic code, but I'd thought I'd also post this as a possible alternative to simply return the count of the unique entries in a range:

Please Login or Register  to view this content.

Regards,

Robert

Hello muralidaran,

Here is the updated macro. This will provide you with a running total in column "B". It has been added to the attached workbook.

Please Login or Register  to view this content.

Originally Posted by sktneer

@hemesh

Good one hemesh.

Thanks Sktneer !

Hi Leith Ross
Thanks for the code, working as required.

Hello muralidaran,

You're welcome. My apologies for not understanding what you wanted initially.