Two Add Filters in One Column - Advance Filter

[siddo]

Hi, I'm very new to using advanced filters in macro. I'm in a situation where I wanted to use two filters in a single column. E.g., there is a column 'Name' in that I have to put a filter on the name which has Pre-Imp and SIC; in this case, is it possible to do it? I know we can use And, Or filters in the advanced filter but think works only when the data is in different columns in my case, the data is in the same column.

Any help would be appreciated in this

 

[Peter_SSs]

Assuming that I have understood correctly, see if you can adapt something like this to your layout & data.

My sample data before:

siddo.xlsm
	A	B	C	D	E
1	Date	Name	Amount
2	1/01/2023	Other 1	1
3	2/01/2023	Pre-Imp	2
4	3/01/2023	Pre-Imp	3
5	4/01/2023	SIC	4
6	5/01/2023	Other 2	5
7	6/01/2023	Other 3	6
8	7/01/2023	Pre-Imp	7
9
10
Sheet1

Sub AF()
  Dim rCrit As Range
  
  With Range("A1").CurrentRegion
    Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
    rCrit.Cells(2).Formula = "=OR(B2=""Pre-Imp"",B2= ""SIC"")"
    .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
  End With
  rCrit.Cells(2).ClearContents
End Sub

After:

siddo.xlsm
	A	B	C	D	E
1	Date	Name	Amount
3	2/01/2023	Pre-Imp	2
4	3/01/2023	Pre-Imp	3
5	4/01/2023	SIC	4
8	7/01/2023	Pre-Imp	7
9
10
Sheet1

 

[siddo]

Thank you for replying Peter, I get the steps that you proposed however in my case I wanted to select values that have pre-imp and SIC in a single cell, not sure if I just use wildcard it would help me over here

 

[Peter_SSs]

If this doesn't do it then can we have a representative sample of data in the name column so that we don't have to guess the sorts of variation that you might have?

siddo.xlsm
	A	B	C	D	E
1	Date	Name	Amount
2	1/01/2023	Other 1	1
3	2/01/2023	Pre-Imp & SIC	2
4	3/01/2023	Pre-Imp	3
5	4/01/2023	SIC or Pre-Imp	4
6	5/01/2023	Other 2	5
7	6/01/2023	SIC, Pre-Imp	6
8	7/01/2023	Pre-Imp	7
9
10
Sheet1

Sub AF_v2()
  Dim rCrit As Range
  
  With Range("A1").CurrentRegion
    Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
    rCrit.Cells(2).Formula = "=AND(SEARCH(""SIC"",B2),SEARCH(""Pre-Imp"",B2))"
    .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
  End With
  rCrit.Cells(2).ClearContents
End Sub

siddo.xlsm
	A	B	C	D	E
1	Date	Name	Amount
3	2/01/2023	Pre-Imp & SIC	2
5	4/01/2023	SIC or Pre-Imp	4
7	6/01/2023	SIC, Pre-Imp	6
9
10
Sheet1

 

[siddo]

Yes, this is the one I wanted, Thank you

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.

Again, not really knowing anything about what is possible in that column, be aware that the code would show a row with, for example, "Supre-Impossible Basics" in the column.

 

[siddo]

Got it, I'm stuck with this scenario again 'TMKOC Tool Custom PPE SIC 3 Pre-imp implement', what should I do in this case?

 

[Peter_SSs]

what should I do in this case?

You haven't said what the problem is or what you want to happen. I can't guess.
With the code I provided, a line with that in the name column would be visible.

 

[siddo]

My problem over here is that my data lies on column F, and I'm unable to modify your code for column F, its not working for me, can you please guide me a little bit?

 

[Peter_SSs]

My problem over here is that my data lies on column F,

All of your data is in column F? I expect not.
What are the columns that need to be included in the filtered data? For example, with my sample data above it was columns A:C.
What is a vacant column that can be used to briefly put the Advanced Filter criteria in? For me it was column D.
Perhaps you could show a sample of at least part of your worksheet (any sensitive data disguised) with XL2BB like I did?