VBA remove unique, triplicates, quadruplicates, keep duplicates

[Bigdanfoley]

I am working with a large data set that requires cleaning due to human error. One of the most obvious errors that people make create unique, triplicate, or quadruplicate entries. A proper entry would always be a duplicate.

I'm looking for some advice (I'm quite new at this) at writing a VBA script to only keep duplicates.

Here is an example of the kind of manual paring I am doing now.

UIN Catagory Value
1001 A 100
1001 B 50
1002 A 100
1002 B 75
1003 A 80
1004 A 1
1004 A 100
1004 B 25
1005 A 120
1005 B 70
1006 A 120
1006 A 124
1006 B 76
1006 B 75
1007 A 120
1007 B 70

to go to

UIN Category Value
1001 A 100
1001 B 50
1002 A 100
1002 B 75
1005 A 120
1005 B 70
1007 A 120
1007 B 70

[alansidman]

Here is a code that I normally use to remove duplicates. I have modified it slightly so that in only removes those that are greater than duplicates. It does delete the whole row.

Please Login or Register  to view this content.

[millz]

In a new column, put this formula =COUNTIF(A:A,A1) and copy down

Filter on this new column, select all and remove only "2". Delete the filtered rows.

[humdingaling]

what you could do...
create helper column, do a countif to see if there is two UIN then filter based on helper, delete helper column

[Bigdanfoley]

In a new column, put this formula =COUNTIF(A:A,A1) and copy down

Filter on this new column, select all and remove only "2". Delete the filtered rows.

what you could do...
create helper column, do a countif to see if there is two UIN then filter based on helper, delete helper column

That has been my main way of cleaning this data up, and I appreciate the confirmation of my solution. I was looking for something that was a bit more elegant. I do very much appreciate the advice.

[alansidman]

Since you have now posted an example, I have changed up my code. Try the following

Please Login or Register  to view this content.

[humdingaling]

Solution in VBA form
assuming your columns of data is A-C

Please Login or Register  to view this content.

[Bigdanfoley]

Here is a code that I normally use to remove duplicates. I have modified it slightly so that in only removes those that are greater than duplicates. It does delete the whole row.

Please Login or Register  to view this content.

Very interesting, I'll try playing around with this. This gets me much closer to where I want to be, except that I need it to remove all rows that have triplicates or more, not just the triplicate row. For example: If there are three 1004 rows, all three must be removed.

[Bigdanfoley]

Solution in VBA form
assuming your columns of data is A-C

Please Login or Register  to view this content.

This seems to do the trick pretty well. I'll see if I can get it to work with my larger excel file. Thanks so much!

[humdingaling]

on larger file
suggest you add the optimisation lines
this at start

Please Login or Register  to view this content.

and of this at the end

Please Login or Register  to view this content.

[kalak]

Another suggestion for the problem as stated.
No need to turn off and on screen updating etc., which only slows this type of code down.

Please Login or Register  to view this content.