Location: http://www.mvps.org/dmcritchie/excel/duplicates.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]
This article addresses aspects of duplicates in rows or cells from identification, suppression, removal or deletion.
(#duplicates)Duplicate identifications (yellowish first among duplicates, greenish for actual duplicates)
formula 1 is:
formula 2 is:
Helper Column to show the row number of the original
Identification of the row number for the original value formula anywhere on row 1
Because of column headers, normally start anywhere on row 2
D E 1 Code ID 2 D2 3 D3 4 D4 5 D3 3 6 D6 7 07-2 8 D8 9 D9 10 D10 11 D3 3
D E 1 Code ID 12 24-3 13 D13 14 D8 8 15 D15 16 D3 3 17 11-1 18 24-3 12 19 07-1 20 07-2 7 21 24-3 12
Additional material on Conditional Formatting and on identifying duplicates with Conditional Formatting.
Suggest looking over Chip Pearson's pages (topic.htm), specifically on Duplicates: Highlighting, Preventing, and Replacing.
There are some pages on my site involving filtering, but suggest you go to Debra Dalgleish's pages on Filtering: AutoFilter, and Advanced
Under Advanced Filtering are two topics on duplicates: Filter Unique Records, and Extract Data to Another Worksheet. [also check out the Criteria topics on the Advanced Filters page).
If you are looking at a Filtered list that view is what you get if you copy and paste, or if you use Mail Merge.
Use AutoFilter to Delete Duplicate Worksheet Entries (Inside Microsoft Office 2000, by Sean Kavanagh, June 2001
Microsoft Excel 12 : Tables Part 6: Removing duplicates from tables (and ranges) of data (blog 2005-11-04, future)
You can use validation to prevent duplicates see contextures.com
Some free addins that might be of interest for usage, and may be more suitable for many tasks than writing your own Worksheet Functions, or Conditional Formatting (above and on another page). though you miss the experience of doing it yourself.
- The Duplicate Master, brettdj (author), add-in to manage Excel duplicates. Found this webpage, not tested but looked adequate.
The use of Conditional Formatting, Filtering, and Validation are all very similar in that the basis is a worksheet formula that returns either True or False, and the formula is based on the active cell and will be adjusted for each cell in the selection..
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2005, F. David McRitchie, All Rights Reserved