+ Reply to Thread
Results 1 to 5 of 5

advanced filter failing to "filter"

  1. #1
    Forum Contributor
    Join Date
    02-28-2013
    Location
    NY, USA
    MS-Off Ver
    Excel 2019
    Posts
    101

    advanced filter failing to "filter"

    When using advanced filter, I noticed that when the function works, it works beautifully.

    However, once in a while, the filter function would fail (i.e. after selecting the list range and the criteria range in advanced filter and click OK, nothing happens to the original content in the List range). When the filter function fails, I have to copy and paste the content and that criteria into a new workbook and repeat the procedure. If I simply repeat the advanced filter operation in the original workbook, somehow I could not get the advanced filter to work.


    Anyone knows how many different reasons the advanced filter would fail to filter a spreadsheet ?

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,336

    Re: advanced filter failing to "filter"

    I don't use filters much. When I have used them and they have completely failed like you describe, it has always been due to some misunderstanding on my part of how the criteria range works. Once I get the criteria correctly entered into the criteria range and the criteria range correctly defined in the advanced filter dialog, the advanced filter has always worked.

    Can you provide a more detailed description of what you have and what you are doing when the advanced filter fails like this? Perhaps a sample file you can upload to the forum? If you can help us recreate this behavior in our own copies of Excel, maybe we can see what is going on.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor
    Join Date
    02-28-2013
    Location
    NY, USA
    MS-Off Ver
    Excel 2019
    Posts
    101

    Re: advanced filter failing to "filter"

    Thanks Guru for your input.
    In all honesty, I am not sure how better to describe the scenario (other than what I've described) and how a sample would illustrate the behavior (as the behavior of advanced filter failing isn't sample-specific, but more likely "operation-specific"). Please do let me know if you have different thoughts.

    One problem I eventually realized was I forgot to use the same heading in my criteria as that in the full table, which is what you've described - "misunderstanding on my part of how the criteria range works".

    No idea what other mistakes were during my operation of the advanced filter and as a result, I don't know what my misunderstanding would have been (on this, how (or where) could I find out more regarding how the criteria range works)

    Two other pieces of observation -
    1 - whatever other mistakes were in the old workbook, once I copied and pasted the original content and criteria into a new workbook and "re-ran" the advanced filter, the output would reflect the result of filtering based on the criteria
    2 - when advanced filter fails, the output shows no filtering of the original content - e.g. a 10x10000 table does not get trimmed to be 10x100, which would have been the "filtered table" after the filter was correctly applied

    I do suspect the failing might be due to, directly or indirectly "clipboard problems"

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,336

    Re: advanced filter failing to "filter"

    No idea what other mistakes were during my operation of the advanced filter and as a result, I don't know what my misunderstanding would have been (on this, how (or where) could I find out more regarding how the criteria range works)
    There are a few tutorials out there. This one, for example: https://www.contextures.com/xladvfilter01.html The most memorable "did not filter at all" that recall was one where I wanted to have an or criteria (multiple rows in the criteria range), so I defined the criteria range to have multiple rows. However, in this scenario, if I left a row blank (because maybe I only needed one criteria for that particular filter), Excel's advanced filter seemed to interpret the blank row as "include records that contain anything", and it did not actually filter anything. If I had few criteria than originally envisioned, I needed to define the criteria range to include only the rows with actual criteria.

    I agree that the problem is most likely operation specific rather than sample specific. However, we need a sample of data in order to test out different operations. We are often lazy and don't want to make up our own dummy data set for testing on. So you can help us help you by providing some kind of sample data set (even if you pull a data set from one of Excel's help files or even the sample file from the contextures tutorial). Then describe the exact operations you are performing on that sample data set to get the "did not filter anything" result, so we can attempt to replicate the behavior in our Excel installations. Maybe then we can help you understand what is going on (and why copying to a blank workbook seems to magically fix the problem).

  5. #5
    Forum Contributor
    Join Date
    02-28-2013
    Location
    NY, USA
    MS-Off Ver
    Excel 2019
    Posts
    101

    Re: advanced filter failing to "filter"

    Sure. Would love to contribute. Will definitely look into the dummy data samples from the help files
    or create one and have it included in my next reply.

+ 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. Replies: 7
    Last Post: 03-17-2014, 01:51 PM
  2. Userform: Make a advanced filter script "filter" when a combobox is altered
    By Zheno in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-09-2010, 04:06 AM
  3. [SOLVED] advanced filter criteria "begins with" and "does not begin with"
    By Eddie O in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 04:05 AM
  4. advanced filter criteria "begins with" and "does not begin with"
    By raph_baril in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  5. [SOLVED] "Criteria Range" in the "Data/Filter/Advanced Filter" to select Du
    By TC in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-11-2005, 10:06 PM

Tags for this Thread

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