Filtering out single values (keeping only repeating data)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to filter out all the values in a column that only appear
once, leaving just items that appear 2+ times? I know there's a way to get
rid of duplicates, but I haven't been able to find a feature that would do
the opposite. Thank you for your insight!
 
Use a help column and a formula

=COUNTIF($A$3:$A$200,A2)<>1


then filter on TRUE


You can do this in one fell swoop using advanced filter, leaving criteria
header blank and the same formula

=COUNTIF($A$3:$A$200,A3)<>1
 
If your data is in column A then in column B you can use this formula:

=IF(COUNTIF(A$2:A2, A2)>1, A2, "")

it will give you the nombers those are more than once.
 
Back
Top