General discussion

Locked

Finding Duplicates in Excel

By Adisha ·
I have a table of serial numbers in Excel - can anybody tell me how I can find duplicates in it. Is it possible to use a filter or a formula?

I look forward to hearing from you soon.

Adisha

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Finding Duplicates in Excel

by Adisha In reply to Finding Duplicates in Exc ...

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!

Collapse -

Finding Duplicates in Excel

by DKlippert In reply to Finding Duplicates in Exc ...

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)

Collapse -

Finding Duplicates in Excel

by DKlippert In reply to Finding Duplicates in Exc ...

Have you solved your problem yet?

Collapse -

Finding Duplicates in Excel

by Adisha In reply to Finding Duplicates in Exc ...

The question was auto-closed by TechRepublic

Collapse -

Finding Duplicates in Excel

by tdembeck In reply to Finding Duplicates in Exc ...

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","")

Collapse -

Finding Duplicates in Excel

by Adisha In reply to Finding Duplicates in Exc ...

The question was auto-closed by TechRepublic

Collapse -

Finding Duplicates in Excel

by RichTee In reply to Finding Duplicates in Exc ...

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

Collapse -

Finding Duplicates in Excel

by Adisha In reply to Finding Duplicates in Exc ...

The question was auto-closed by TechRepublic

Collapse -

Finding Duplicates in Excel

by Adisha In reply to Finding Duplicates in Exc ...

This question was auto closed due to inactivity

Back to Software Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums