Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts

    Delete rows matching any of a long list of items

    I'm looking for advise on how to proceed with a procedure to clean out unneeded data from a large database kept in an Excel 2007 spreadsheet. The database is currently 200K items, and what i want to accomplish is to remove any row where one of the cells matches any of about 300 different items.

    The exclude list is in a separate sheet beginning in cell A1, and has over 300 string entries in separate rows in the format used for Advanced Filter, e.g. "*this?and?that*" so if any of the database cells in the criterion column contains the words "this", "and", and "that" separated by one character, that would be a match and that row should be deleted.

    I've considered writing a macro to successively filter the database for each entry and delete all visible rows, but this process has to be performed when the database is updated, so I'm trying to design a time-efficient process from the start.

    I started looking through the examples on ExcelForum, OzGrid and VBAExpress I found through Google, though most are only using a single criteria.

    Looking for suggestions and other references from the Excel-eratti!

    TIA!!!
    PJ in FL

  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
    It looks like you cross-posted this and have an answer at http://eileenslounge.com/viewtopic.php?f=27&t=6182

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    Steve,

    I did cross post as I've noticed quite a difference in expertise between the membership at the two lounges, although I do know some (myself included) frequent both.

    My apologies if the cross-post violates forum rules (like Rule 14 - OOOPS!).
    Last edited by pjustice57; 2011-04-20 at 13:15. Reason: added relevant rule reference
    PJ in FL

  4. #4
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    Update: Based on other information, I'm looking at using Advanced Filter with the rows selected as multiple criteria. Anyone know just how many rows can be "OR"ed in a single Advanced Filter call?
    PJ in FL

Posting Permissions

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