none
Filter data (from 1 column) based on more than 2 and/or criteria RRS feed

  • Question

  • Hi Experts,

    I have a data table like below and I want to filter entire table based on just one column i.e "Country"

    First Name Last Name Country
    Elizabeth Morrison Philippines
    Maria Lawrence Russia
    Elizabeth Ford Russia
    Maria Hamilton China
    Elizabeth Davis China
    Gregory Gonzales Russia
    Gregory Washington Vietnam
    Elizabeth Morris Philippines
    Maria Alexander Greece
    Gregory Morales France
    Elizabeth Turner Greece

    Here is the criteria:-

    1. Contains text "u" And
    2. Contains text "e" OR
    3. Contains text "g" 

    This will result in filtering out below data for 9/11 rows:-

    First Name Last Name Country
    Maria Lawrence Russia
    Elizabeth Ford Russia
    Gregory Gonzales Russia
    Elizabeth Morrison Philippines
    Gregory Washington Vietnam
    Elizabeth Morris Philippines
    Maria Alexander Greece
    Gregory Morales France
    Elizabeth Turner

    Greece

    The above criteria has only 3 conditions however I have to filter it at least 6 times using and/or and "contains text" feature. Unfortunately excel allows for up to 2 criteria's only:

    Image

    I tried creating a template with embedded and/or controls against the search input fields which creates (based on and/or selection and inputted text) a criteria range on the right like below and record a macro to use advanced filter to take this as criteria range and filter results, this however not producing desired results.

    First Name Last Name Country Country Country
        *u  *e  
           *g
           

    How do I make this work for at least 6 conditions? Can VBA make this happen, can somebody provide a code - It would be a huge help, I am trying to achieve this since 12 days and driving me crazy :(

    I really appreciate your time and help!!!

    Best,

    Bunty


    Bunty


    • Edited by bunty_1244 Thursday, December 1, 2016 7:05 PM
    Thursday, December 1, 2016 7:04 PM

All replies

  • In H column I pasted below criteria. That will do the job..

    Country
    *u*
    *e*
    *g*










    In Advanced Filter box, criteria range will be H1:H4. To use OR, you have to put condition in separate rows..

    Or can use below formula in M2 cell. Criteria range will be M1:M2

    =SUMPRODUCT(ISNUMBER(SEARCH({"u";"e";"g"},E4))*1)>0


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol


    Friday, December 2, 2016 1:07 PM
    Answerer
  • Hi,

    You can do that by using Advanced Search as well

    Paste the below formula in Adjacent column in excel anywhere (Do not change the header Name Country.. it should be same as your column header in the table)

    Country Country
    ="=*e*" ="=*u*"
    ="=*g*" ="=*u*"

    Note:

    Little info: Values across columns are treated as "AND" and conditions in same column but multiple rows are considered with OR condition. This is the reason I have set up the criteria range like above.

    Now go to Advanced filter in Excel, (Data Tab --> Data and Filter --> Click on Advanced)

    Select the range of your table in List Range

    Select the criteria range where ever you have pasted the above

    and click ok.. 

    It will do the Job.


    Vish Mishra

    Friday, December 2, 2016 1:40 PM
  • Hi there,

    Did you try any of the solutions provided below? If yes, provide your feedback.

    Regards,

    Vishwa


    Vish Mishra

    Tuesday, December 6, 2016 9:55 AM
  • Hi Vishwa,

    Thank you so much for your prompt response!!

    I am still having few issues and will get back soon.

    I really appreciate your time and help.

    Best,

    Keyur Parekh


    Bunty

    Monday, December 12, 2016 7:57 PM
  • Appreciate your input Javed!!

    Bunty

    Monday, December 12, 2016 7:57 PM