Creating a new list of repeating data in a column

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

Guest

I work for a service company. I am working with a list of sites. Most of
the sites that have mulitple visits. I need a formula that will go through
the column and allow me to create a list of all sites that have more than 3
visits to them or repeat 3 or more times.

Any help?
 
Assuming site names in column A, visit dates in column B, headers in row 1 and
data in rows 2:1000, use a "helper" column, say C. Put this formula in C2 and
copy it down through C1000:

=IF(AND( COUNTIF(A$2:A2,A2)=1, COUNTIF(A$2:A$1000,A2)>=3 ),"X","")

If A2 contains the 1st occurrence of the site in the list and the count for
that site is >= 3, it shows an "X", otherwise "blank". AutoFilter to show only
those rows with an X in column C.

Or you could create a pivot table, using the site as both the row and data
fields. Again you would need AutoFilter to show only rows with counts >=3.
 
Back
Top