How to: Quickly remove duplicated records

At this moment ASAP Utilities already contains several tools to manage duplicates.
However a special tool for duplicate records was still missing... until now!

You could already identify or remove duplicated records with a workaround by merging the cell contents into a temporary added column and then use ASAP Utilities' "Conditional row and column select, hide or delete..." tool on this new column to remove the duplicated rows. But that involved a few extra steps and it isn't as fast as this new utility.

In the new version of ASAP Utilities you will be able to quickly remove all duplicated records from your sheet:
Remove duplicated records

Besides removing the duplicates from a single sheet, you can also choose the option "Remove the records from Dataset 1 that are found identical in Dataset 2". This can for example be used if you have two sheets with addresses and the persons on one of the sheets have already be contacted. You can then remove those from the first sheet to get a list of people that still need to be contacted.

You can also choose the following options:

  • Case sensitive.
    If you choose this option then the comparison is made case senstive. This means for example that "Bastien" does not equal "BASTIEN".
  • Ignore leading, trailing and excessive spaces (example: "bastien" equals " bastien")
    If you choose this option than it doesn't matter if some cells have irregular spacing. This can be useful if the data isn't always entered properly. Especially trailing spaces sometime cause problems when it comes to comparing lists.
  • Hide the rows instead of removing them

Download and use

You can read more information and download the pre-release of the new version from:
//www.asap-utilities.com/faq-questions-answers-detail.php?m=220

You can start this new (experimental and only in English) tool via:
ASAP Utilities Options » New tools in development (experimental, English only)
and then click on the button "Manage duplicated records":
Manage duplicated records

One comment

Phil

Interesting, very interesting ;-)

that will save me a lot of time ;-)

If you want to manage this within a VBA-solution, this piece of code would be a good starting point:

Sub RemoveDupes()

Range("RANGE").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("NEWRANGE"), _
Unique:=True

End Sub

Two notes:
– Replace RANGE with the original range you want to remove duplicates from
– Replace NEWRANGE with the range you want to copy the new duplicate-free content to

I have struggled with this problem quite some time so the post from Bastien come just in time to mention my findings :-)

Phil