Finding Duplicates in Excel

I know it is easy enough to carry out a "find duplicates" query in Access 2000 on any table imported from Excel - I just wondered if there was a comparable procedure in Excel. . .

Many thanks in Advance!

You can easily find unique records by using Data>Advanced Filter and choosing unique records.
For more information on identifying and working with duplicates, look at Chip Pearson's web site. He has ways to test for, highlight and tag duplicates.
http://www.cpearson.com/excel/duplicat.htm

(There are no spaces in the URL)

One quick way to find duplicates is to use the "Filtering". Select the column header over the column where the duplicates may exist. Then, click on menu items: DATA | FILTER | AUTO FILTER. Now when you select the drop down at the top of the column, you can if there are any duplicates. Another method that may work for you is to sort the column and add a formula in the column to the right of the one containing your data. Once you create the formula you can copy it down the right-hand column. If a duplicate exists, the word "DUPLICATE" will appear next to it. Here's the formula:
=IF(D6=D5,"DUPLICATE","")

Another solution to find duplicates would be to use the ?Count? function with ?SubTotals?.

To use this method:
First: Sort the data in ascending order based on the column containing the serial numbers.

Second: Select ?Subtotals?? from the ?Data? menu.

In the ?for each change in?: select the serial number column
In the ?Use function?: Select ?Count?
In the ?Add subtotal to:? Check the serial number column.
Accept the default check boxes for showing the subtotals at the bottom of the form.
Click ?OK

This will give you all sub totals of the serial numbers. By expanding and contracting the list you will be able to see if you have any duplicates (any number above 1) and you will be able to see exactly which serials numbers are in question.

Good Luck,

Rich

This question was auto closed due to inactivity