Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Chicago
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    filter out single entries (xp)

    Hello

    I have a spreadhseet with a list of names in column A. How can keep only the names that appear 5 times or more and delete the rest?
    Thanks much - Nancy

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: filter out single entries (xp)

    I will assume A1 has some header. Insert a new column B (it is temporary)
    You can add a formula in B2:
    =countif(A:A,A2)

    copy/ autofill this formula from b3 to the bottom of the data. This is the number of times the items appear
    Select A1
    Data-filter - autofilter
    select the filterlist in B1
    Custom
    Left box: is less than
    Right box: 5
    <ok>

    Select all these filtered rows and edit - delete row
    The data - autofilter to remove the filter
    Delete column B

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: filter out single entries (xp)

    How about

    Assume your list is in column A place this formula in B1:

    =IF(COUNTIF(A:A, A1)>5,TRUE,FALSE)

    This will show all the entries that are repeated more than 5 times (assuming all unique)

    Sort on Column B and delete all the TRUE options.
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filter out single entries (xp)

    If A1 is not a column header (field name), insert a new row in row 1, and enter a column header in A1, e.g. Name.
    In B1, enter Count.
    In B2, enter the formula <code>=COUNTIF(A:A,A2)</code>
    Fill down as far as needed.
    Select Data | Filter | AutoFilter.
    Click the dropdown arrow in B1.
    Select Custom...
    Specify Less Than 5 as condition, and click OK.
    Only rows with a count of less than 5 will be displayed.
    Select those rows and delete them.
    Turn off AutoFilter.
    You can now delete column B (and row 1, if desired)

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Chicago
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filter out single entries (xp)

    As usual, you all saved the day.
    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •