Advanced Filter: Multiple <>

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Advanced Filter: Multiple <>

  1. #1
    Board Regular
    Join Date
    Sep 2003
    Location
    Houston, Texas
    Posts
    492
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Advanced Filter: Multiple <>

     
    I'm using advanced filter on a list, and I'm trying to use multiple "does not equals". Here's what I've got:

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book4___Running: xl2002 XP : OS = Windows XP
    (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
    F
    1
    Int'lRegCountryDirMgr Rep
    2
    <>NA
    3
    <>DL
    4
    5
    6
    7
    DirMgrRep
    8
    NXBSLGRGM
    9
    NXBNBCGAL
    10
    NXBSLGCLW
    11
    DLDLMH
    12
    DLDLGK
    13
    PGOKACSBA
    14
    NXBSLGERM
    15
    NXBSLGJPB
    16
    PGOKACBMM
    17
    DLPMJR
    18
    MJMMLWDXS
    19
    PMPBLSCMS
    20
    MJMJMMJMK
    21
    PMPBLSBPB
    22
    DLDLAM
    23
    NANADAS
    24
    PMP PAKHRM
    25
    PMP DWBDTT
    26
    PMPDWBJPB
    Sheet2

    [HtmlMaker 2.42] 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.


    I'm not sure why this is failing to filter out DL and NA. When I enter just one criterion (e.g. <>DL), the filter works, but when I attempt multiple <>s, it does not.

    What am I doing wrong?

    Many thanks,

    Bill

  2. #2
    Board Regular
    Join Date
    Jun 2003
    Location
    Houston
    Posts
    287
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Advanced Filter: Multiple <>

    If the condition is an 'and' it must be contained in the same row.

    Multiple rows are 'or's.

    Try two 'DIR' columns with each having one of the criteria
    Brad

  3. #3
    Board Regular
    Join Date
    Sep 2003
    Location
    Houston, Texas
    Posts
    492
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Advanced Filter: Multiple <>

    Brad - thanks. Your suggestion to use multiple DIR columns worked. I didn't realize I could do that - I thought my criteria headings had to match my source data headings. Definitely good to learn otherwise.

    Thanks again,

    Bill

  4. #4
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Advanced Filter: Multiple <>

      
    Bill,

    If your criterion in row 2 is a formula, then row 1 must be blank. The following works too.

    D2:
    =AND(A8<>"NA",A8<>"DL")

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows XP
    (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
    F
    G
    H
    1
    Int'lRegCountry DirMgr Rep
    2
    TRUE
    3
    4
    5
    6
    Output Range:
    7
    DirMgrRep DirMgrRep
    8
    NXBSLGRGM NXBSLGRGM
    9
    NXBNBCGAL NXBNBCGAL
    10
    NXBSLGCLW NXBSLGCLW
    11
    DLDLMH PGOKACSBA
    12
    DLDLGK NXBSLGERM
    13
    PGOKACSBA NXBSLGJPB
    14
    NXBSLGERM PGOKACBMM
    15
    NXBSLGJPB MJMMLWDXS
    16
    PGOKACBMM PMPBLSCMS
    17
    DLPMJR MJMJMMJMK
    18
    MJMMLWDXS PMPBLSBPB
    19
    PMPBLSCMS PMP PAKHRM
    20
    MJMJMMJMK PMP DWBDTT
    21
    PMPBLSBPB PMPDWBJPB
    22
    DLDLAM
    23
    NANADAS
    24
    PMP PAKHRM
    25
    PMP DWBDTT
    26
    PMPDWBJPB
    Sheet2

    [HtmlMaker 2.42] 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.


    Regards,

    Mike

User Tag List

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