Excel VBA 24-Hour Trainer, 2nd Edition

Lesson 15Handling Duplicate Items and Records

When you work with data in tables or lists, it is common for some items to appear more than once. Two situations usually arise when duplicate items exist, depending on the nature of the work at hand:

• The repeated items are unwanted and need to be deleted. For example, if you are compiling a list of e-mail addresses, or you are gathering a list of people's names for invitation to an event, you would only want a list of unique items.
• Items are expected to be repeated in the list and need to be maintained for analysis or record-keeping. For example, a list of monthly payments made to a vendor would show that vendor's name with each transaction.

Deleting Rows Containing Duplicate Entries

Suppose a table of data contains duplicate items in one or more columns. To delete rows containing duplicate items, the first step is to determine if the table contains duplicates in just one column, or if several (maybe all) columns contain duplicate data.

Deleting Rows with Duplicates in a Single Column

Suppose you have a list of items that are repeated in column A. The macro named DeleteDupesColumnA uses AdvancedFilter to expose the first instance of every item in column A. The exposed rows are marked with a value (the numeral 1 in this example, but it could be any value) in a helper column. All rows with empty cells in the helper column are deleted.

NOTE For my money, AdvancedFilter is the second-most powerful tool in Excel, behind pivot ...