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]

Hi and welcome to MrExcel.

Just to clarify some terms.

5 rows, 70,000+ column entries

I assume you mean 5 columns for example columns A, B, C, D, E and 70,000+ rows.

You could put a sample of Tab1, let's say the 5 columns and about 20 rows. If they are confidential data, replace them with generic data.
To put samples use XL2BB tool, look at my signature.

Keywords are Tab 2

You could put a sample of Tab2

filters out around 10 words, filters in around 15 words

Using your previous examples, you could explain how you want the filters. Put a sample of tab3 with the result after making the filters.

 

[Jason12354]

DanteAmor: Yes I meant 5 columns not 5 rows. What is the XL2BB tool?

Here's a sample of tab 3 after using Excel's 2 Custom Auto Filters. I want to use a filter which can simultaneously filter in rows which contain/do not contain the words in Tab2 (around 10+ words filter in/10+ words filter out). Here is a P drive link to the Data with filter applied, and a screenshot showing Excel's Custom Auto Filter of 2 words out (which is not enough) Snippet of database (2 words used to Filter Out).

 

[DanteAmor]

What is the XL2BB tool?

It is a tool to paste cell ranges here, like the following:

200706-sample-code-multi-filter v1.xlsm
	A	B	C	D	E
1	Number	Keyword (want word/phrase filtered in)		Number	Keyword (want word/phrase filtered out)
2	1	HVAC		1	Urinal
3	2	Pump		2	Sink
4	3	Filter		3	WC
5	4	AHU		4	Toilet
6	5	Ventilation		5	Shower
7	6	E.O.T		6	Ladies
8	7	Crane		7	Bidae
9	8	EOT		8	Microwave
2. Keyword

Check this: XL2BB - Excel Range to BBCode

Taking advantage of the above, I passed the keyword out to columns D and E, in this way you can grow down both lists.

The filtered data (Tab 3) needs to autolink to the original bulk data (Tab 1) as the bulk data download will occur monthly.

I don't understand that part, so let's start with this.
I made 2 macros, one to filter keywords out and one to filter keywords in:

Both macro autofilter on sheet1

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

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

Try both macro and tell me what the next step is.

__________________

 

[Jason12354]

DanteAmor: Thanks for providing the macro.

I want to just verify that the macro will enable filtering out of at least 10 keywords and filtering in of at least 10 keywords, will this macro do this? And at which point can I put the keywords in?

 

[DanteAmor]

Yes, the macro can filter 2 or more items.
Put the keywords on sheet2 according to the following:

200706-sample-code-multi-filter v1.xlsm
	A	B	C	D	E
1	Number	Keyword (want word/phrase filtered in)		Number	Keyword (want word/phrase filtered out)
2	1	HVAC		1	Urinal
3	2	Pump		2	Sink
4	3	Filter		3	WC
5	4	AHU		4	Toilet
6	5	Ventilation		5	Shower
7	6	E.O.T		6	Ladies
8	7	Crane		7	Bidae
9	8	EOT		8	Microwave
2. Keyword

But I gave you 2 macros, one to filter IN and one to filter OUT.
Is not that what you want?
If not, then I am not understanding what you need.

__

 

[Jason12354]

Yes you did, apologies I didn't see that within the VBA code it refers to Tab2.'keyword' for filtering out/in. I will run it now and see if it works. Thanks

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Jason12354]

Also there is a VBA macro code to auto run macros,


Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet3").AutoFilter.ApplyFilter
End Sub

As I want the filters to automatically update without me having to manually filter should I use this code too?

 

[Jason12354]

Hi DanteAmor,

I am trying to implement the VBA code into a different worksheet (which due to my job I can't share with you).

I need to know how to amend keybits of the code

The cell references have changed.

Please can you explain what, with reference to the code:

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

What "D2" and "A2" refer to?
What does End(3) refer to?

And with reference to the code

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

What "A1:E" refers to?

Thanks