+ Reply to Thread
Results 1 to 2 of 2

Excel 2010 Advanced Filter

  1. #1
    Registered User
    Join Date
    12-02-2012
    Location
    Miami
    MS-Off Ver
    2019, 365, MAC
    Posts
    45

    Excel 2010 Advanced Filter

    I use the same "filter" on 8 different workbooks. The data is automatically imported, so using the same filter criteria is erased. Is there a way to create a filter "group" to exclude the same items?

    For example, there are items we exclude from analyzing in every report. To filter them out each time is a nuisance. Below is a samle of the items we currently filter out. (more will be added as time goes by).
    Any assistance or Excel examples would be greatly appreciated. (note: I do not use VBA)

    EXCLUSIONS Collapse NodeEXCLUSIONS - EXCLUSIONS
    10220 [10220] - MANUAL DISBURSEMENTS
    10280 [10280] - AUTOMATED PAYROLL
    12101 [12101] - AR - CABLE SERVICES
    12105 [12105] - Accs Rec-Ad Sales
    14010 [14010] - PREPAID PROGRAMMING
    14072 [14072] - PREPAID RENT - POLES
    21430 [21430] - TRADE REFUNDS PAYABLE
    21470 [21470] - ACCRUED POLE RENT
    21560 [21560] - ACCRUED FRANCHISE FEES
    21694 [21694] - ACCRUED 911 FEE
    21726 [21726] - Short Term Sponsorship Liabili
    21950 [21950] - ACCRUED PROGRAMMING COSTS - LO
    50500 [50500] - Movie
    50550 [50550] - DIGITAL MOVIES PROGRAMMING
    50570 [50570] - Event
    60210 [60210] - LOCAL INCENTIVE PROGRAMS
    61115 [61115] - REPAIRS & MAINTENANCE - PLANT
    64120 [64120] - UTILITIES
    64140 [64140] - POLE RENT
    64240 [64240] - OTHER TAXES AND LICENSES

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Excel 2010 Advanced Filter

    I can't be too specific without an example worksheet.

    In general... my approach would be to create a seperate tab, call it "Lookup" for this example, and have a list of all excluded items in that tab. Let's say that it's in A1:A100 of that sheet.

    Then, I would smack a dummy column on the end of your table of imported data, and fill the first row, and then autofill down:
    Please Login or Register  to view this content.
    (This assumes the code is in A1 of whatever column you are importing in).

    This will look and see if the value in A1 matches any of the values in the list, and return the position. But because we set the match type to "0", if it doesn't find a match, it will return an error code. We wrap the MATCH with the ISERROR function to create a boolean to feed to the IF.

    So all you do is Filter the dummy column for "Use" and remove the "Don't Use."

    Then, whenever you add or remove an item for filter, you add/remove from the lookup list. Then, the criteria for the IF changes, but the filter itself operating on the list stays exactly the same.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. excel 2010 - reasons why advanced filter returns ALL ROWS and not criteria set?
    By wyattea in forum Excel Programming / VBA / Macros
    Replies: 33
    Last Post: 09-13-2013, 08:38 PM
  2. advanced filter - button to re apply advanced filter across multiple sheets
    By motmac87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2013, 11:16 PM
  3. Excel 2010 Advanced Function Filter problems - 2007 issues
    By adamwestrop in forum Excel General
    Replies: 3
    Last Post: 07-29-2011, 01:52 PM
  4. using advanced filter in excel
    By bschrand in forum Excel General
    Replies: 1
    Last Post: 03-14-2006, 05:25 AM
  5. [SOLVED] advanced filter a range:Advanced Filter function
    By Il Principe in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1