Duplicates

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.

Conditional Formatting (#condfmt)

(#duplicates) Duplicate identifications (yellowish first among duplicates, greenish for actual duplicates)
formula 1 is: =IF(COUNTIF($D:$D,$D1)>1,COUNTIF($D$1:$D1,$D1)=1)
formula 2 is: =IF(COUNTIF($D:$D,$D1)>1,COUNTIF($D$1:$D1,$D1)>1)
Helper Column to show the row number of the original

Identification of the row number for the original value formula anywhere on row 1
=IF(COUNTIF($D$1:$D1,$D1)>1,MATCH($D1,$D$1:$D1,0),"")
Because of column headers, normally start anywhere on row 2
=IF(COUNTIF($D$1:$D2,$D2)>1,MATCH($D2,$D$1:$D2,0),"")

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.

General Material on Duplicates (#general)

Suggest looking over Chip Pearson's pages (topic.htm), specifically on Duplicates: Highlighting, Preventing, and Replacing.

Filtering out Duplicates (#filtering)

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)

Preventing Duplicates (#preventing)

You can use validation to prevent duplicates see contextures.com

Addins strictly for Duplicates (#addins)

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..

See the references at the end of each topic above.

Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions. Posting suggestions and netiquette. More information on newsgroups and searching newsgroups. Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).

This page was introduced on May 4, 2005. [My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]