find duplicates in list

A

Ann

i have a single column of data. how do i highlight the duplicates in the
list. i've tried conditional formatting and can't get the formula right.
some rows might be duplicated 4 times and others just 2. tia
 
R

ryguy7272

Try these:
=IF(COUNTIF($A$1:$A$55,$A1)>1,$A1,"")

=IF(AND(COUNTIF($A$1:$A$55,A2)>1,COUNTIF($L$1:L1,$A2)<1),$A2,"")


Note: these functions count uniques
=SUMPRODUCT(1/COUNTIF(A1:A55,A1:A55))
=SUM(--(FREQUENCY(A1:A55,A1:A55)>0))
=COUNT(1/FREQUENCY(A1:A55,A1:A55))

HTH,
Ryan---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top