Finding duplicates in an Excel list

  Craig.m 11:56 24 Feb 2005

In Access you can compare two lists to find duplicates. I am trying to merge two lists in Excel and would then like to find any duplicates in a specific column. Is there an easy way to do this? Would there be a simple piece of VB that could do the task?

Secondly in my search to find this solution I did find a piece of data validation that could be used to highlight if a duplicate entry was added to a column. It does work and it could be useful but the question I have is: How does it work?

The formula to put in the custom validation is =COUNTIF($A$2:$A$20,A2)=1. Should you try to enter a value that is a repetition of earlier in the column, it will error, just can't see how it does it.


  GANDALF <|:-)> 12:12 24 Feb 2005
  Whaty 12:48 24 Feb 2005

Here you go:-

Merge the two lists into one column, lets say column 'A'.

Use 'Data' and 'Sort' on this column, this will put all the duplicates together.

In another column enter the following:


Filter your new column on the blanks and you will have just one of everything..

Hope this helps,


  Craig.m 13:36 24 Feb 2005

Thanks for the quick replies,.

Gandalf, your download looks ideal - unfortunatley PCs at work do not allow anything to be loaded to them.

Terry, I think I will have to resert to your solution but it is not easy as the two sets of data are whole spreadsheets in their own right. It will be a little complicated merging them. I just wanted to run a comparison of one column in each to see if there were any duplicates.

I may even resort to the Access solution and put them in two tables.

As to how that statement above works - anyone got a simplistic descrition of how it works?


  recap 13:48 24 Feb 2005

If you do a search in Excel's help menu like "Filter a list by using advanced criteria" you may find the answer to your question?

  Whaty 13:59 24 Feb 2005

Craig, I may have mis-interpreted your question.? My formula will identify the duplicates but it does require the list to be in sort order...

The formula you have found counts the number of times an entry appears in a list regardless of its position and turns it into a TRUE / FALSE statement.

COUNTIF counts the number of entries that meet a condition.

$A$2:$A$20 is the range of cells the formula will check (the $ sign fixes the position).

A2 is the condition to be met.

=1 turns it into a TRUE or FALSE statement

So, if whatever appears in A2 appears anywhere else in the range A2 - A20 it will count the number of times it appears, and because it is more than 1 it will say FALSE.. if it only appears once (=1) it will say TRUE..

The only problem with this is, if you set your range $A$2:$A$100 and there is nothing in the cells from say, A70 down, when you copy your formula down it will show FALSE for the empty cells....

Hope this helps, Terry..

  Craig.m 22:41 24 Feb 2005

I was thinking of using the formula in a validation. I can't understand the formula in that it seems to compare A2 to the range, but it does stop any repetition happening anywhere in the list not just a recurrence of the A2 value.

It is just getting it straight as to how it works so that I can adapt it to other situations


  EdFrench 23:06 24 Feb 2005

A simple (and possibly off the wall) solution would be to import the streadsheets into Access while not allowing duplicates to be listed.
You would then be left with a single list which you could set to Excel.

  EdFrench 23:06 24 Feb 2005

Read Spreadsheets

  Whaty 12:41 07 Mar 2005


Sorry for the 'slightly' belated reply....

A2 is not a fixed reference (no $ sign) and therefore as you copy it down the row it becomes A3, A4, A5, etc..

Regards, Terry

  Whaty 13:12 07 Mar 2005

Sorry, should have been 'copy it down the column' not 'row'...

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Nokia 8 vs Galaxy S8

Best new design books 2017: From vintage infographics to the the psychology of type

iMac 21.5-inch (2017) review

Nokia 8 : design, caractéristiques techniques, date de sortie