Finding Duplicate Records

  • Thread starter Thread starter Erlang
  • Start date Start date
E

Erlang

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.
 
You could add another column with formulas like:

With headers in row 1, put this in B2:
=countif(a:a,a2)
(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
records.

Hope this helps.

Pete
 
I have created one complex sorting/duplicate example

If You are interested look :)
http://www.sarv.ee/ftp/henn/excel

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
 
Back
Top