Finding Duplicate Records



I've got a column with duplicate records. I want to be able to pull
records that has the most duplicate. For example, on the column, I've
got 3245,3546,3245,5467,3245. Since 3245 is a duplicate, I want to be
able to pull all the duplicates. To go further, is there a way to
limit the number of duplicates I want to view. For example, I want to
be able to pull records with 10 or 20 duplicate records. Please help.

Dave Peterson

You could add another column with formulas like:

With headers in row 1, put this in B2:
(and drag down the length of your data.)

Then apply data|filter|autofilter

You can filter to show the records that are between 10 and 20 (use Custom).


Not sure what you mean by "pull" a record. You can obtain a list of
the distinct records by copying the column (with a header) to a new
sheet and then applying Advanced filter to it, selecting Unique
Records Only in the pop-up box. Then you can use a COUNTIF formula to
count the number of records in the original list against each unique
value, and this could easily be sorted in descending order to give you
your list of most common records.

You can apply Autofilter in your original list to see particular

Hope this helps.


Henn Sarv

I have created one complex sorting/duplicate example

If You are interested look :)

the name of example is sortingandunique.xls

When You like - enter names unique and duplicates in 1st column
and fill down formulas on next 5 columns

Look what happend in J and M
try to enter something in cells P8 and R8

any feedback and comment is welcome :)

Henn Sarv

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question