Consulting

Results 1 to 8 of 8

Thread: VBA Advanced filter help..... Criteria excluding a list

  1. #1

    VBA Advanced filter help..... Criteria excluding a list

    Hi Team,


    I Need your help in advanced filter, I have Data in Datasheet, and Criteria in Criteriasheet


    In Criteria sheet.
    I have 5 indian batsman list
    and 5 Australian Batsman as Criteria list


    I want to apply advanced filter on my data, I want to keep only 5 Indian Batsamns and 5 Australian Batsmans Record.
    by removing unwanted data after advanced filter.


    Attached is my input file and my attempted code which is not working, plz assist how to achieve this task and how to make dynamic list Thanks.


    Regards,
    mg
    Attached Files Attached Files
    Last edited by malleshg24; 12-04-2019 at 01:45 PM. Reason: Output sheet Added

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,772
    Do you want to keep all instances of Australian batsman Ricky Pointing regardless of the Indian batsman?
    It might be easier if you submit another file marking in column C the rows you want to keep.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Hi Paul,

    I have added output sheet after filtering manually the data,
    if I come across any cell either Indian Batsman or Australian batsman,
    from that Criteria Range, I will not delete those, Rest I will delete.


    Regards,
    mg

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,772
    Now you've really got me wondering what the logic is;
    You gave me:
    2019-12-04_211005.jpg

    I've been working on giving you either:
    2019-12-04_211142.jpg

    or:
    2019-12-04_211234.jpg

    Which one?
    If it's the first (yours) you'll need to explain the logic a bit more.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Hi Paul,

    Sorry for confusion my mistake , here I am expecting Second Result which are displayed, below line
    I've been working on giving you either:



    Regards,
    mg

  6. #6
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,772
    See attached.
    Attached Files Attached Files
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    Hi P45Cal,

    it is working as expected, I liked your formula, thank you so much for your help,

    What will be opposite of this formula..... where I want to delete indian and Australian batsmans Name.

    =OR(ISNA(MATCH(Data!#REF!,$A$2:$A$6,0)),ISNA(MATCH(Data!#REF!,$B$2:$B$6,0)) )


    Regards,
    mg

  8. #8
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,772
    put NOT() around the current formula.

    Alternatively:
    =AND(ISNUMBER(MATCH(Data!A2,$A$2:$A$6,0)),ISNUMBER(MATCH(Data!B2,$B$2:$B$6, 0)))
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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