Dealing With Duplicate Records in Microsoft Excel
It's obvious that our sample file has duplicate records. There's many ways to
get rid of them or highlight them.
Finding With a Formula
Use this method if you only need to get rid of duplicates once
in a while.
We insert a new Column A by clicking on A and hitting InsertàColumn.
We give the row a heading. If we don't, and we sort the data,
we might discombobulate it!
We type a formula into A2 that will find duplicates for us.
This formula only looks for duplicate values in Column B.
If you need to look for duplicates in more than one column,
you can use a formula like this one instead.
We then copy the formula by double-clicking the fill handle.
With the data still selected as a result of copying the formula, we then copy
the cells (Ctrl+C or EditàCopy), and then we
choose EditàPaste Special, Values, OK. We do
this to remove the underlying formula.
We now sort descending by Column A to bring all the Dupes to
Select all the rows that have "Dupe" in Column A, and delete
Delete Column A.
Finding With Conditional Formatting
Perhaps you only want to find the duplicate values so you can
deal with each manually. For this you can use Conditional formatting. Just
select the cells you want highlighted if there are duplicates. From the menu,
choose FormatàConditional formatting. Choose the
options as shown below, and type the formula in as shown, being sure to include
the entire range of your cells.
Finding or Highlighting Using VBA
If you need to deal with duplicates often, you'll want to use
OfficeArtilces.com debuted on May 26, 2005.
MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on
this web site are provided "as is" and we do not guarantee that they can be used
in all situations.
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered
trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.
All contents © 1998-2014 by MrExcel Consulting | All rights reserved