Multi filter by word on Excel 2016 document

[Jason12354]

Hi,

This is my first post please be nice. I am creating a Windows 10 Excel 2016 document which takes a bulk data download on Tab 1 (5 rows, 70,000+ column entries), filters out around 10 words, filters in around 15 words (Keywords are Tab 2) and displays the filtered data (Tab 3) as 5 columns and around 8,000 rows. Excel's custom autofilter can only filter on 2 different words. The filtered data (Tab 3) needs to autolink to the original bulk data (Tab 1) as the bulk data download will occur monthly. I need to know how to use Excel 2016 to filter in around 15 words and filter out around 10 words. Please let me know how to do this? Thanks

 

[DanteAmor]

a = sh1.Range("D2", sh1.Range("D" & Rows.Count).End(3)).Value2

  'sh1 is "1. LIVE data" sheet
  'D2 is start cell, to las cell of column D
  a = sh1.Range("D2", sh1.Range("D" & Rows.Count).End(3)).Value2
  'Rows.Count).End(3) = Means: of the number of rows in the sheet, of that row move up (3) or (xlup)

b = sh2.Range("A2", sh2.Range("A" & Rows.Count).End(3)).Value2

  'sh2 is "2. Keyword" sheet
  'A2 is start cell, to las cell of column A
  b = sh2.Range("A2", sh2.Range("A" & Rows.Count).End(3)).Value2  
  'Rows.Count).End(3) = Means: of the number of rows in the sheet, of that row move up (3) or (xlup)

sh1.Range("A1:E" & sh1.Range("E" & Rows.Count).End(3).Row).AutoFilter 4, dic.keys, xlFilterValues
What "A1:E" refers to?

It is the range of cells to be filtered from A1 to column E and the last row with data on sheet sh1 = "1. LIVE data" sheet

 

[Jason12354]

Hi DanteAmor,

I have found a much simpler way to do what I want. Instead of filtering out multiple words and then filtering in multiple words I can instead just filter in.

I have found a much simpler Excel VBA code online for achieving this. I want to, on a single Excel tab, filter in the following words:

"Word1"
"Word2"
"Word3"
"Word4"
"Word5"

I have written the Excel code for achieving this below:

Sub FilterRows()

Worksheets("Sheet10").Range("A3").AutoFilter Field:=2, Criteria1:="Word1", Criteria2:="Word2", Criteria3:="Word3", Criteria4:="Word4", Criteria4:="Word5"

End Sub

"Sheet10" is the tab number, do I need to use the actual tab name?
"A3" is the first row with data on
Field:=2. The column containing the words to be filtered out is Column B.

When I apply this it says "Runtime Error 9 Subscript out of range". Can you please let me know why it isn't working? Thanks

 

[DanteAmor]

I have found a much simpler Excel VBA code online for achieving this.

Glad to hear you found something simpler.

 

[Jason12354]

Hi DanteAmor: Sorry for changing VBA approach halfway through, I do appreciate the time you put into writing your previous VBA script. The reason why I changed to the simpler code is that nobody in my area knows how to use VBA and it's not something that my colleagues are going to learn. So the VBA script needs to be as simple as possible.

From the below VBA script

Sub FilterRows()

Worksheets("Sheet10").Range("A3").AutoFilter Field:=2, Criteria1:="Word1", Criteria2:="Word2", Criteria3:="Word3", Criteria4:="Word4", Criteria4:="Word5"

End Sub

could you please let me know why, when I Run the script, on the tab it runs on, the error message "Runtime Error 9 Subscript out of range" appears? Thanks

 

[DanteAmor]

Autofilter method, maximum can use 2 criteria

Range.AutoFilter method (Excel)

Office VBA reference topic

docs.microsoft.com

 

[Jason12354]

Hi Dante,

The tab headings for my Excel file (drive link: 20200730-sample-code-multi-filter v6.xlsm - Shared with pCloud) are below:

'1.Data'

Contains the keywords to filter in and to filter out.

'2.Data Filter out'

To filter out words from cells: '1.Data B37:B50'(Tab 1, cells B37:B50) on column range B3: B40000

'3.Data Filter in category 1'

To filter in words from cells: '1.Data C54:C58' (Tab 1, cells C54:C58) on column range B3: B40000

'4.Data Filter in category 2'

To filter in words from cells: '1.Data C59:C60' (Tab 1, cells C59:C60) on column range B3: B40000


Could you please let me know how to adapt the previous VBA code you wrote for filtering out (applied to Tab '2.Data Filter out', below)

Sub FilterData_Out()
Dim sh1 As Worksheet, sh2 As Worksheet, dic As Object
Dim a As Variant, b As Variant, exists As Boolean
Dim i As Long, j As Long

Set sh1 = Sheets("1. LIVE data")
Set sh2 = Sheets("2. Keyword")
Set dic = CreateObject("Scripting.Dictionary")
If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
a = sh1.Range("D2", sh1.Range("D" & Rows.Count).End(3)).Value2
b = sh2.Range("E2", sh2.Range("E" & Rows.Count).End(3)).Value2

For i = 1 To UBound(a)
exists = False
For j = 1 To UBound(b)
If LCase(a(i, 1)) Like "*" & LCase(b(j, 1)) & "*" Then
exists = True
Exit For
End If
Next
If exists = False Then dic(a(i, 1)) = Empty
Next

sh1.Range("A1:E" & sh1.Range("E" & Rows.Count).End(3).Row).AutoFilter 4, dic.keys, xlFilterValues
End Sub

and filtering in (applied to tabs '3.Data Filter in category 1' and '4.Data Filter in category 2', below,)

Sub FilterData_In()
Dim sh1 As Worksheet, sh2 As Worksheet, dic As Object
Dim a As Variant, b As Variant, exists As Boolean
Dim i As Long, j As Long

Set sh1 = Sheets("1. LIVE data")
Set sh2 = Sheets("2. Keyword")
Set dic = CreateObject("Scripting.Dictionary")
If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
a = sh1.Range("D2", sh1.Range("D" & Rows.Count).End(3)).Value2
b = sh2.Range("A2", sh2.Range("A" & Rows.Count).End(3)).Value2

For i = 1 To UBound(a)
exists = False
For j = 1 To UBound(b)
If LCase(a(i, 1)) Like "*" & LCase(b(j, 1)) & "*" Then
exists = True
Exit For
End If
Next
If exists = True Then dic(a(i, 1)) = Empty
Next

sh1.Range("A1:E" & sh1.Range("E" & Rows.Count).End(3).Row).AutoFilter 4, dic.keys, xlFilterValues
End Sub

Thanks,

 

[DanteAmor]

Sorry, but I am not understanding the requirement. I hope someone else can help you.