Results 1 to 5 of 5

Help whit Advanced Filter

This is a discussion on Help whit Advanced Filter within the Excel Questions forums, part of the Question Forums category; Hi, Assuming range A20:A100 to be filtered; Range A1:A4 has restrictions for filtering; A1 and A20 are label cells of ...

  1. #1
    New Member
    Join Date
    Aug 2002
    Location
    Brasil
    Posts
    25

    Default

    Hi,
    Assuming range A20:A100 to be filtered;
    Range A1:A4 has restrictions for filtering;
    A1 and A20 are label cells of course
    I want hidde cells whit 0 (zero), MALE and CLUB, and displays all others.
    What must I type in A2=> (>0)???, A3=>(>MALE)???, A4 (>CLUB)???. I do it but don't work, it hidde only zeros (hi). Help, how do!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,900

    Default

    On 2002-08-24 14:30, Mauricio Samy Silva wrote:
    Hi,
    Assuming range A20:A100 to be filtered;
    Range A1:A4 has restrictions for filtering;
    A1 and A20 are label cells of course
    I want hidde cells whit 0 (zero), MALE and CLUB, and displays all others.
    What must I type in A2=> (>0)???, A3=>(>MALE)???, A4 (>CLUB)???. I do it but don't work, it hidde only zeros (hi). Help, how do!
    Am I understanding correctly that you want filter A2:A100 in sheet X to a sheet Y by applying the conditions/criteria that a filtered value must not be MALE or CLUB or 0?


  3. #3
    New Member
    Join Date
    Aug 2002
    Location
    Brasil
    Posts
    25

    Default

    Thanks Aladin!
    More simple! Only want aplly a local filter.
    Filtered at current sheet.




  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,900

    Default

    Mauricio,

    Advanced Filter can be at times unnerving...

    Let A1:A15 house the following sample:

    {"Items";1;0;3;"CLUB";6;"Brasil";0.6;"College";10;0;"MALE";"Nadia";6;5}

    where "Items" is a label.

    ( 1.) Format the label in a distinct manner, e.g., in bold and italic.

    ( 2.) In C1 enter: New

    which is a label.

    ( 3.) In C2 enter:

    =NOT(OR(A2={"CLUB","MALE",0}))

    which constitutes a computed criteria, saying that the the entry filtered must not equal either CLUB or MALE or 0 (zero).

    ( 4.) Click in A2.

    ( 5.) Activate Data|Filter|Advanced Filter.

    ( 6.) Check Copy to another location.

    ( 7.) Enter $A$1:$A$15 (if not already there) in the box for List range.

    ( 8.) Enter $C$1:$C$2 in the box for Criteria range.

    ( 9.) Enter, say, $E$1 in the box for Copy to.

    (10.) Leave Unique records only unchecked.

    (11.) click OK.


    See the figure...


    ******** ******************** ************************************************************************>
    Microsoft Excel - aaAdvFilter Mauricio.xls___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    1
    Items*New*Items
    2
    1*TRUE*1
    3
    0***3
    4
    3***6
    5
    CLUB***Brasil
    6
    6***0.6
    7
    Brasil***College
    8
    0.6***10
    9
    College***Nadia
    10
    10***6
    11
    0***5
    12
    MALE****
    13
    Nadia****
    14
    6****
    15
    5****
    Sheet1*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Aladin

  5. #5
    New Member
    Join Date
    Aug 2002
    Location
    Brasil
    Posts
    25

    Default

    Great Aladin!
    Thanks a million
    A very interest and intelligent way to set criteria in advanced filter, not found in "search tools", so clear as you explained.
    I'll use it and credit to you of course!

    _________________
    Thanks,
    Mauricio Samy Silva
    Windows-98/Excel-2000
    I love this Site

    [ This Message was edited by: Mauricio Samy Silva on 2002-08-24 16:21 ]

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com