Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Advanced Filter - Best practice (97:SR2)

    Is it possible to use the advance filter (copy to another location) for multiple criteria? The best I can explain it is to have you look at the attached document. The report I'm working on has two rows of criteria. If I filter the data based on row one's criteria, row two goes is over written.

    Any suggestions would be appreciated.
    John

  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: Advanced Filter - Best practice (97:SR2)

    Not sure exactly what you are after. Your example is NOT clear (at least to me)

    You can filter on as many criteria as you want.
    Anything in 1 row ANDs all the individual columns criteria
    If you have criteria items in 2 rows
    All row1 items are ANDed together, All row 2 items are ANDed together, but you get a filter of row1 OR row2.

    Also not sure about the OVERWRITTEN aspect. Are you using the criteria range as your OUTPUT? The output headings and the criteria headings are 2 different beasts.

    If this does not answer your question, could you attach a generic table with the crits set and let us look at it.

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced Filter - Best practice (97:SR2)

    Steve,

    I have attached a sample file. I believe the issue with the vanishing criteria row2 has to do with the filtering of the first criteria. I would prefer to have the filter place the data between the two criteria rows (increasing/decreasing rows as needed).

    Thanks,
    John

  4. #4
    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: Advanced Filter - Best practice (97:SR2)

    The criteria you list must contain the headers of interest AND the CRITERIA you want

    The OUTPUT range must ALSO contain the headers of interest (can be different than the criteria headers). Your example has neither. Your criteria ONLY lists the headers, but no criteria. On what criteria do you want to filter? The way it is now you will just copy ALL the data.

    Also I have found it best to have the output range with NOTHING underneath the output and then only select the headers as the output.

    If you don't want excel to overwrite items underneath, you must EXPLICITLY tell excel what range it may use (eg c11:h18) and IF excel gives you an error that the range is NOT big enough, click NO to not continue (if you click YES to continue, it will overwrite) then insert MORE rows, then run again with the larger range selected. You will have to keep expanding the range until it is large enough otherwise you will overwrite.

    If you only choose 1 row, you have told excel that is "OK" to overwrite anything underneath it!

    If you want excel to automatically add rows, you will have to write your own "adv filter" macro to check the number of matches and then insert rows. the built-in routine does NOT do this automatically.

    Steve

Posting Permissions

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