lkpederson
01-20-2019, 04:21 PM
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
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