Results 1 to 6 of 6
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    'unique' Filter criteria (Excel 2000)

    I have a VBA routine that sets up the criteria range for extracting data from a table.
    Unfortunately, the results I get are not what I want.
    For example, if I set say, a value of "CAR" in my criterra range, the data I get will include records for "CARAVAN" as well as CAR. I only want those for "CAR"
    Excel seems to interpret the criteria as "CAR*" rather than an exact match of "CAR" only.

    Any suggestions?

    (Or do I have to make sure that all my criteria elements are unique and not partially included as a 'prefix' for other elements?)

    zeddy

  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: 'unique' Filter criteria (Excel 2000)

    Create a "formula" criteria. It should be in the criteria range but contains no header label:
    The following assumes that the data source is in col A and the header row is 1 so the first data row is 2:

    <pre>=A2="car"</pre>


    You can make the formula as complicated as you want.

    Steve

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: 'unique' Filter criteria (Excel 2000)

    Steve

    Many thanks!

    Now, if I want to use multiple criteria (i.e. this OR that OR whatsit)
    do I just include simila 'formulas' in separate rows in the criteria range?
    i.e. does each 'formula' look like
    =A2 = "THIS"
    =A2 = "THAT"
    =A2="WHATSIT"

    zeddy

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: 'unique' Filter criteria (Excel 2000)

    If you have Car as the criteria, excel will return all elements that commence with 'Car". If you have =Car as the criteria excell will return exact matches. You can enter =Car by either formatting the relevant criteria cell as text, or preceeding it with a single quote , i.e. '=Car.

    Andrew C

  5. #5
    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: 'unique' Filter criteria (Excel 2000)

    Criteria in the same row are ANDed

    Criteria in different row are ORed

    Be aware if you remove listings from a criteria row so all are blank you will have an OR with no criteria so not filering will be done. If you remove all the elements from a row you should change the size of your criteria range

    Also you can use OR in a formula (leave label blank)
    =or(A2 = "THIS",A2 = "THAT",A2="WHATSIT")

    Steve

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: 'unique' Filter criteria (Excel 2000)

    Thanks Andrew and Steve!

    Brilliant!

    You have saved me hours and hours of work.

    zeddy

Posting Permissions

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