Find Duplicates in a List with Formula

Finding duplicates in a range can be done with Excel formula or VBA.  The following method shows how Excel formula can be used to identify any values which are duplicated in a range of cells.  In order to find the duplicated items in a list, say in column A, use this Excel formula in B2 copying the formula down to the last used row in Column A.

=COUNTIF($A$10:A10,A10)>1

All of the cells which are duplicates are in green, conditional formatting was used with the above formula to produce this result.  If you use the Excel formula in the cells adjacent to the values you are testing duplicates will be True.   If you wanted to remove these you could put the Autofilter on and filter by True and remove the entire row of all items which meet this criteria.

You may wish to find the duplicates between two specific lists and the formula above can be adapted to suit.

=COUNTIF($J$10:$J$32,A10)>0

Where the list you are checking is in cells J10:J32 and A10 is the cell which you are looking to see if it is in that list.  Exel formula will display True if there is a match.

There are other ways to do this if the process needs to be part of an automated procedure then the following might be more helpful Find Duplicates with VBA  It outlines the same procedure but with the assistance of vba.

 The following Excel file is a working example of the above technique.