Look up a duplicate entry in a column

Posted by Chris on November 07, 2001 12:24 PM

I have a column of data that is about 2000 lines long. I have a duplicate of one entry and need to find wich one it is. Is there a quick lookup function that can do this?

Posted by Richard S on November 07, 2001 12:41 PM

COUNTIF

Assuming your data starts in A1
=COUNTIF($A$1:$A$2000,A1) in cell B1, and copy down to B2000. The formlua will return the number of occurences of the value in A1 in the range A1:A2000. Then you can search for the value 2 to find the duplicate. Alternatively you can sort your data in ascending order on the results, and all the duplicates will be at the top of the file.
HTH
Richard

Posted by giacomo on November 07, 2001 1:05 PM

In a parallel column paste this formula in the second row of data and then copy down to the last row

=MATCH(A2,$A$1:A1,0)

If there is no match it will return N/A, if there is a match it will return the row number of the match.

Posted by Chris on November 07, 2001 1:27 PM

Thanks a lot guys.