Remove duplicates and count their number - faster method needed

Hi snb,

Let me tell you that you wrote a very strong (clever) code however somehow when i am trying the same it is not giving me the expected numbers. What i was trying to do to cross check the accuracy is i tried pivoting the raw dump and counted the number of instances of a particular value and the numbers doesn't match.

Hi Ciprian,

Although snb provided a wonderful code which i think might need a quick mind boggling and a fix which i am sure snb will do it (if he reads this and he doesnt proves me wrong ), i would suggest you to write a simple match formula to see whether there are any duplicate values or not and also you can create a patch which will count the number of duplicate records.

Let me know if you would need further help on the same.

snb,

Thanks for the explanation however thats exactly what i was trying to explain that it works most of the time however fails in some scenarios.

If you pick up the attached sheet and just copy the first 200 values from column A and run this code on it. You will see the difference after comparing it with a pivoted data.

snb,

i got the point, basically the example you have given is only for 200 rows and thats where the confusion was. My mistake sorry .
Inconvenience caused is regretted

For what it's worth, a Pivot Table did a nice job too... ( see attached)

I know that a Pivot table would to a great job too, but for what i need this is the way to go.

thank you for the code

the below code is bit faster

Please Login or Register  to view this content.