Deleting duplicates in Excel, removing duplicated rows

Duplicates Manager for Excel: developer's description

This very handy MS Excel add-in will make management of Excel duplicates a breeze. It is designed to assist you when you have duplicates in 1 or more Excel lists and/or tables.

With Duplicates Manager for Excel you can:

Excel Duplicates manager

• Create a list of unique data
• Display only unique data
• Create a summary report
• Color code all duplicates, up to 3 levels
• Prevent users from adding duplicates to a list or table
• Compare 2 lists for matching entries
• Remove duplicates from any range
• Remove entries from one list that exist in the other
• Remove duplicate rows from a table
• Remove all but first occurrence of duplicates
• Fill blanks with the entry directly above

Duplicates Manager: Create a list of uniques from duplications

If you have a list that contains duplicate entries it can be a very time consuming process to manually remove them! The bigger the list, the more time consuming it is. The Duplication Manager has an option called Create Unique List that will create a fresh copy of your list on a new Worksheet that only contains one occurrence of each entry!

This option is ideal for a large list of email addresses, names, items or anything!

Duplicates Manager: Display a list of uniques from duplications

If you have a list that contains duplicate entries it can be a very time consuming process to manually hide them! The bigger the list, the more time consuming it is. The Duplication Manager has an option called Display Only Uniques that will hide all duplicates in your list showing only one occurrence of each entry!

Duplicates Manager: Create a summary list of duplications

The option Summary Report will take your list and create a mini Pivot Table report. This will tell you at a glance how many times each entry in the list occurs and a total count of one occurrence of each entry. You can then use the Pivot table created to hide/show specified entries in the list.

Duplicates Manager: Color code duplicates in a list or table

The option Highlight Duplicates will color code all entries, appearing more than once, in a list of table of data. If an entry occurs twice it will have a Green background, if it appears three times it will have a Yellow background. Any entry occurring more than three times will have a Red background. The table, or list range will also be dynamic in that, if you add more entries and that entry then appears 2, 3 or more times, it will be color coded accordingly.

Duplicates Manager: Prevent duplicate entries in list or table

The option Prevent Further Duplicates will prevent you, or another user from entering entries that already exists in your table or list. When/if you try to add an entry that occurs already, you will be shown a pop-up message telling the user the entry already exists.

As they say, Prevention is better than cure!

Duplicates Manager: Compare 2 lists for duplicates

The option Compare Two Lists will create a mini Pivot Table report. This will tell you at a glance how many times each entry in each list occurs and a total count of one occurrence of each entry. You can then use the Pivot table created to hide/show specified entries in the list.

There will also be a single list of combined entries.

Duplicates Manager for Excel: Remove duplications from a range or table

The option Create a list of unique data is just the shot for a removing duplications from a single column list. However, there are time when you have duplications in a range, or table, that consists of more than one column. The option Remove Duplicates will do this for you very quickly!

Duplicates Manager: Remove items from a list that occur in another list

The option Compare 2 lists for matching entries is ideal for showing you which items appear in both lists, but does not remove them. The option Remove Entries from "List Range" that Occur in "List 2 Range" will do this in the blink of an eye.

As it creates a copy, your original 2 lists are left unchanged should you need them.

Duplicates Manager for Excel: Remove duplicate rows from a table

The option Create a list of unique data is ideal for removing duplicate entries in a single column list. However, there are times when you have a table of data where some rows are simply duplicates of other rows. The option Create Unique Table will remove all these duplicated rows and create a copy of your table where all rows are unique.

Duplicates Manager: Remove all but first occurrence of duplicates

It is quite common to have a table of data where some entries in a particular column have been repeated over-and-over. Sometimes, for varying reasons, you need to remove these repeated entries leaving only the first occurrence. Then leave the cells in-between blank. The Create Blanks Between option is what is needed.

This option is ideal for a large table of any data where one column in the table has duplicate entries repeated over and over. See example below. Bolded entries are the only ones which will remain after running Create Blanks Between. It is the reverse of Fill Blanks Between.

Before

Before - Remove all but first occurrence of duplicates

After

After - Remove all but first occurrence of duplicates

Duplicates Manager: Fill blanks with the entry directly above

It is quite common to have a table of data where some entries in a particular column have been left out to signify a repeat . This then means you are unable to use many of Excel's standard features like Pivot Tables, Sort and many others. The option Fill Blanks Between will fill in these missing entries with the entry directly above.

This option is ideal for a large table of any data where one column in the table has blank cells to signify repeats. See example below. It is the reverse of Create Blanks Between.

See also in "Excel - Find and remove duplicates in Excel "

• Duplicate Remover for Excel - Find & remove Excel duplicates.
• Fuzzy Duplicate Finder for Excel - Finding & correcting fuzzy duplicates and typos in Excel.