Advanced Filter using multiple values in the same criteria

[johnnytominaga]

Hey guys!

Is it possible to run an Advanced Filter that accounts for multiple values under the same criteria?

Here's what I'd like to achieve:

BEFORE:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Phone[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]John[/TD]
[TD]99999999[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Paul[/TD]
[TD]22222222[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sara[/TD]
[TD]99999999[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Vanessa[/TD]
[TD]11111111[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Andrew[/TD]
[TD]99999999[/TD]
[/TR]
</tbody>[/TABLE]

ADVANCED FILTER CRITERIA:
ID<>1
ID<>3
ID<>4
Phone = 99999999

RESULT:

[TABLE="width: 500"]
<tbody>[TR]
[TD]5[/TD]
[TD]Andrew[/TD]
[TD]99999999[/TD]
[/TR]
</tbody>[/TABLE]

My actual dataset has over 7000 rows and over 30 columns. I'm trying to edit the following code (and multiple formats to add data to Cell B6), but haven't had any success:

Sheet1.Range("$A$1:$C$5").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range('Worksheet'!$A$6:$B$7"), CopyToRange:=Range( _
"'Worksheet'!$A$9:$A$13"), Unique:=False


I'm using Excel 2016 on Windows 10.

Thanks for your help as always,


Johnny

 

[PhilS2520]

Hi Johnny,

First I should apologize and say that there is some logical mishaps here, as far as I can paint the picture you are trying to convey.

Is ID unique or not?

If ID is unique then you do not need to use it. This will leave phone number.

Then you have to decide why you want Andrew, but not John or Sara. What makes the Andrew record different from John and Sara?

So, sorry I do not understand what you are trying to do without knowing how you pick the record you want to get.

 

[johnnytominaga]

Hey Phil!

No need for apologies. Thanks for responding

Both IDs and phone numbers repeated throughout the source range. I want to basically filter them out.

If it’s not possible to do it via advanced filter, I guess I can loop through the IDs columns, compare them, remove repeated items’ ranges and sort the results list again, right?

I was wondering if there’s a more simple solution though.

Let me know your thoughts.

Thanks again.

 

[Peter_SSs]

Here is one way using Advanced filter. I have assumed that the original data is on a sheet called 'Data'. Edit the code to match your sheet name.
On either sheet (I've used 'Worksheet') choose anywhere there are 2 vertical vacant cells (I've used F1:F2) and enter a formula as shown below that reflects the conditions you want to meet into the bottom of those 2 cells.

Book1
	F
1
2	FALSE
Worksheet

Cell Formulas
Range	Formula
F2	=AND(Data!A2<>1,Data!A2<>3,Data!A2<>4,Data!C2=99999999)

Then use advanced filter code like this

Sheets("Data").Range("A$1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Worksheet").Range("F1:F2"), _
    CopyToRange:=Sheets("Worksheet").Range("A9")

 

[johnnytominaga]

Thanks a lot for your response Peter.
That's exactly what I was looking for. I'd never thought of having a blank header and then just filtering the conditions. Brilliant!

Thanks again and have a great day!

Here is one way using Advanced filter. I have assumed that the original data is on a sheet called 'Data'. Edit the code to match your sheet name.
On either sheet (I've used 'Worksheet') choose anywhere there are 2 vertical vacant cells (I've used F1:F2) and enter a formula as shown below that reflects the conditions you want to meet into the bottom of those 2 cells.

	F

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]FALSE[/TD]

</tbody>

Worksheet​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=AND(Data!A2<>1,Data!A2<>3,Data!A2<>4,Data!C2=99999999)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Then use advanced filter code like this

Sheets("Data").Range("A$1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Worksheet").Range("F1:F2"), _
    CopyToRange:=Sheets("Worksheet").Range("A9")

 

[Peter_SSs]

Thanks a lot for your response Peter.
That's exactly what I was looking for. I'd never thought of having a blank header and then just filtering the conditions. Brilliant!

Thanks again and have a great day!

You are very welcome. Glad it suited your requirement.