filter sublist from list in another column then filter that with another criterion

[lkpederson]

I have a spreadsheet containing thousands of lines of data. It is organized by client name (one of the criteria) and for each client name, there are hundreds of rows of CAS numbers, some of which may be repeated. I have another column which contains a subset of the CAS numbers and this is the second criteria. I've written out an example of the data.

Example:

Data:
Client Name CAS CAS sort column (actually a couple hundred rows long)
1 10-100-1 12-200-3
1 12-200-3 34-32-45
1 34-32-45
1 55-00-10
2 10-100-1
2 12-200-3
2 34-32-45
2 55-99-10
3 10-100-1
3 12-200-3
3 34-32-45
3 55-00-10

Result wanted:
1 12-200-3
1 34-32-45
2 12-200-3
2 34-32-45
3 12-200-3
3 34-32-45

I'm trying to figure out how to do a 2 tier filter where I filter by the client name then the CAS sort column or the other way around.

What I've tried:
I've tried to autofilter and, after sorting on the CAS sort column, only the last CAS number found in both columns was visible. Ignore the comment marks...

With pRng'        
        .AutoFilter Field:=16, Criteria1:=F_2 ' filters to nothing
        .AutoFilter Field:=16, Criteria1:="=1"
        .AutoFilter Field:=1, Criteria1:="=1" ', Operator:=xlAnd, _
          Criteria2:=("=1") ' filters to nothing
 End With

I've tried range.advancedfilter but can't see how to filter on two different columns. I've also tried hiding the client names that I don't need for that round. I can do that with a For next loop.

I can do this if I use a "helper" column with match() function between the CAS numbers and the CAS sort column then filter for matches.

Is there a way to perform a 2 tier / stepped filter or just stick with my helper column?

TIA

[Paul_Hossler]

Regular Filter will do 2 or more columns, or you can create a Critera Range (yellow) and use Advanced Filer


Capture.JPG

AND's are on a single row and OR's are on other rows:

(Client Name = 1 AND CAS = "12-200-3") OR

(Client Name = 1 AND CAS = "34-32-45") OR ……

Advanced Filter can also use operators, >500, <>123, etc. The link has good examples and explaination

Sub FilterData()
    Dim r As Range
    
    Set r = ActiveSheet.Cells(1, 1).CurrentRegion
    
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    
    r.AutoFilter
    r.AutoFilter Field:=1, Criteria1:="=1", Operator:=xlOr, Criteria2:="=3"
    r.AutoFilter Field:=2, Criteria1:="=12-200-3", Operator:=xlOr, Criteria2:="=34-32-45"

End Sub

'https://www.ablebits.com/office-addins-blog/2016/09/07/excel-advanced-filter/
Sub FilterData_2()
    Dim r As Range
    
    Set r = ActiveSheet.Cells(1, 1).CurrentRegion
    
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    
    r.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("E1:F5"), CopyToRange:=Range("H1:I1"), Unique:=False
End Sub

[lkpederson]

Here's what I tried:

     Set r = ActiveSheet.Cells(1, 1).CurrentRegion    r.AutoFilter
    r.AutoFilter Field:=1, Criteria1:="=1"
    r.AutoFilter Field:=16, Criteria1:=F_2, Operator:=xlOr

The first filter I just set to "1" as a test; it worked. On the second autofilter, I needed to use a named range for the second filter ("12-200-2", etc.) because there's over 200 criteria so typing them individually didn't make sense. The named range, "F_2", includes the label which matches the label in Column P (i.e. 16). and this filters out everything.

Addendum: The second method (and also using "Advanced Filter" in the "Formulas" tab worked by creating a monster criteria table of all the CAS numbers for each Client Name which ends up being almost as long as the data.

thx