+ Reply to Thread
Results 1 to 11 of 11

How to filter in many criteria?

  1. #1
    Registered User
    Join Date
    12-05-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    77

    How to filter in many criteria?

    Dear Expert,

    I have a workbook uploaded based on the center washing machine.

    Could you guide me on how to filter more than 1 criterion as I have shown at the workbook "Expected Results Shows after filter"?
    I have tried the excel -> data -> advanced data but it can only show 1 data which my management is looking for more than 1 criteria range.

    Hope to hear from you.

    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    5,730

    Re: How to filter in many criteria?

    Try Filter like this:
    Stick to each location
    Attached Images Attached Images

  3. #3
    Registered User
    Join Date
    12-05-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    77

    Re: How to filter in many criteria?

    Hi bebo021999,

    There is a catch, the expected results that our management want to know is without Boston - LG under Quarantine.

    So, do you have any other idea?

    Thanks.

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325

    Re: How to filter in many criteria?

    Just filter " quarantine" out in the Status column

  5. #5
    Registered User
    Join Date
    12-05-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    77

    Re: How to filter in many criteria?

    Hi, arthurbr,

    Thanks for the simple filter.

    I have uploaded the same workbook but this time I have added a few rows as I key in into the table.

    Could you filter the same Expected Results?

    Hope to hear from you soon.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325

    Re: How to filter in many criteria?

    It's all quite easy. Open each filter in turn (click the arrow in the header) and check the items you want to display in your filtered data

  7. #7
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,142

    Re: How to filter in many criteria?

    Quote Originally Posted by fgfgicare View Post
    Hi, arthurbr,
    I have uploaded the same workbook but this time I have added a few rows as I key in into the table.
    Could you filter the same Expected Results?
    If you need more detail than arthurbr's advice above then more information needed.

    OK so far:
    i1) Filter "Location" to see only LA, Boston, New York
    2) Filter "Status" to exclude "Quarantine"


    What are the rules whereby the following are to be excluded?

    Boston Moyo New Centre
    New York Panasonic Repair
    New York LG New Centre

    Does order matter? The order of your expected results is different to your B5:D23 table, but the expected results don't appear to follow any obvious sort rule.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

  8. #8
    Registered User
    Join Date
    12-05-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    77

    Re: How to filter in many criteria?

    Dear GeoffW283,

    Thanks for your concern. Yes, order matters.

    If I am using your method to filter "location" and "status" to exclude only "Quarantine", it doesn't work as the following below need to be excluded as well.


    New York Panasonic Repair
    Boston Moyo New Centre
    New York LG New Centre

    Do you have any other ideas or functions or formula to filter only the expected results as shown in the workbook?

    Kindly advise with thanks.

  9. #9
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,142

    Re: How to filter in many criteria?

    If its not possible to provide a general rule for what is to be excluded or what the sort order should be then does the following approach work for you?
    1. Remove any existing autofilter
    2. Add a "sort" column (column-A), title "Sort" in A5.
    3. Populate the sort column rows with numbers that specify the sort order. Leave column-A rows blank if that row is not to be displayed.

    You should now have something like this:

    fgfgicare.png

    1. Reapply the autofilter to include the "Sort" column.
    2. In the A5 dropdown select Sort Smallest to Largest
    3. In the A5 dropdown uncheck "blanks"


    Your list should now exactly reflect your expected results.
    You can hide col-A if you wish.

  10. #10
    Registered User
    Join Date
    12-05-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    77

    Re: How to filter in many criteria?

    That is a Brilliant idea, GeoffW283!

    Thank you very much.

  11. #11
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,142

    Re: How to filter in many criteria?

    Glad it worked for you. Thanks for the feedback and reputation points

+ 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. Userform filter with criteria (Advanced Filter)
    By nordicdust in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 04-02-2019, 08:16 AM
  2. Replies: 0
    Last Post: 08-04-2017, 02:39 AM
  3. Macro to filter a pivot whilst looping through filter criteria in a variable list.
    By Alistairm88 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-29-2015, 04:25 AM
  4. Replies: 1
    Last Post: 12-12-2014, 06:46 AM
  5. Replies: 6
    Last Post: 10-16-2014, 08:42 AM
  6. [SOLVED] Advanced Filter Code : Criteria Range More Than 1 Row Breaks Filter
    By PaulGW in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-02-2014, 08:54 AM
  7. Advanced Filter : One Criteria Cell Breaks the Filter - Returns Only Headers
    By PaulGW in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2014, 10:06 AM

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