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

[malleshg24]

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

[p45cal]

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.

[malleshg24]

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

[p45cal]

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.

[malleshg24]

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

[p45cal]

See attached.

[malleshg24]

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

[p45cal]

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)))