¨ FrontPage ¨ Outlook ¨
¨ Miscellaneous ¨
It's obvious that our sample file has duplicate records. There's many ways to get rid of them or highlight them.
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 the top.
Select all the rows that have "Dupe" in Column A, and delete them.
Delete Column A.
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.
If you need to deal with duplicates often, you'll want to use VBA.
This entry deletes duplicates in a specified range. http://www.vbaexpress.com/kb/getarticle.php?kb_id=135
Chip Pearson provides many ways to deal with duplicates. http://www.cpearson.com/excel/duplicat.htm
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