MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel - find duplications

Posted by Ann on July 05, 2001 6:56 PM

I would like to know how to find duplicates in a worksheet column of figures.

Posted by Joe on July 05, 2001 8:19 PM

Sort the file. Then place the following formula alongside the sorted list
starting at the 2nd row after the start of the list:
(Assuming the duplicates are in column A and list begins at row 1)
@IF(a1=a2,"DUP",""). Copy the formula to all the rows.

If you want to retain the original sort order, just add a column and fill down
with sequential numbers. After sorting and flagging dups, convert the duplicate
flags to labels by copying in place using Paste Special Values. Then you can
restore the original order using the column of numbers you added and all
duplicates will retain their flags.


Posted by zen on July 06, 2001 12:35 AM

Re: You use conditional formating

using conditional formating wil highlight the dups in anyway you want (font, cell col etc.)
goto Format_Conditional Formating... select Formula is then use =COUNTIF(A:A,A1)>1, then change the formating to say, Red Pattern, all the dups will show Red. (change the column letter to the one you need)