Formula to find number of times value has repeated in Excel

Question

We have an Excel file with at least two thousand entries we want to know how to find out if any entry has been repeated twice and what entry that is. We just want the Entry which has been repeated two times and which entry it is

Please help

Answer 1

For a series of entries in column C starting in row 2 enter the following in cell D2

=COUNTIF(C:C, C2)

or

=if(COUNTIF(C:C, C2) >1,COUNTIF(C:C, C2),"")

and copy D2 down as far as the data goes.

The first formula gives a count of how many cells contain the value in column C of the same row.  The second show values in column D only when they are over 1, but you can change that to only those over 2, only those equal to 2, etc. by changing >1 to >2, =2, etc.

And, of course the formula column doesn't have to be D, it could be any column and/or usually hidden to prevent interference with print or normal display requirements.

Answer 2

Your data in column A with heading. Type in cell B2 =COUNTIF(A2,A:A). Copy this down as far as required. Do a sort  then a custom filter on result where result >=2. This will pick out all the duplicated data.