Searching more than one keyword in a column in a power query table

Ice2Cool

New Member
Joined
Sep 24, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I require some assistance please. I have created a power query table with returns a table to Sheet1 in my spreadsheet. One of the columns of the table is “Description” that describes the characteristics of a part. I want to be able to set a cell or text box to search for all matching rows that match all keywords within that text box.

for example, if I have the following rows:
CAPSMD 100PF 1206
CAPSMD 200PF 1206
CAPSMD 0603 200PF

and I have searched the term “1206 200PF”,
It should return only:
CAPSMD 200PF 1206

this is a basic example but essentially I am trying to do a “match on all words” search. The order of the words shouldn’t matter. And I need to be able to search by numerous keywords.

can anyone please assist or direct me on how to do this please?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,549
Office Version
  1. 365
Platform
  1. Windows
@Ice2Cool, welcome to the forum
I have an example of a searchable combobox , see if it suit your requirements.

VBA Code:
Option Explicit
'=============== ADJUST THE CODE IN THIS PART: ===================================

'sheet's name where the list (for combobox) is located. [in the sample: sheet "deList"]
Private Const sList As String = "deList"

'cell where the list start [in the sample: cell A2 in sheet "deList" ]
Private Const sCell As String = "A2"

'the linked cell (cell that link to the combobox)
Private Const xCell As String = "B3"

Private vList
Private c As Range
Private d As Object

Private Sub ComboBox1_GotFocus()
Dim x
With Sheets(sList)
    vList = .Range(sCell, .Cells(.Rows.Count, .Range(sCell).Column).End(xlUp))
End With
    Set d = CreateObject("scripting.dictionary")
    d.CompareMode = vbTextCompare
    
    ComboBox1.MatchEntry = fmMatchEntryNone
    ComboBox1.Value = ""
    'ComboBox1.ListRows = 10 'to show how many item
End Sub

Private Sub ComboBox1_Change()
Dim z, ary

With ComboBox1
    If .Value <> "" Then
        If IsError(Application.Match(.Value, vList, 0)) Then
        Call get_filterX
           .List = d.keys
           .DropDown
        Else
            Range(xCell) = .Value
        End If
    Else
        Range(xCell) = .Value
        .List = vList
    End If
End With
End Sub


Sub get_filterX()
'search without keyword order
Dim i As Long, x, z, q
Dim v As String
Dim flag As Boolean
    
    d.RemoveAll
    z = Split(UCase(ComboBox1.Value), " ")

    For Each x In vList
        flag = True: v = UCase(x)
            For Each q In z
                If InStr(1, v, q, vbBinaryCompare) = 0 Then flag = False: Exit For
            Next
        If flag = True Then d(x) = Empty
    Next

End Sub

The example:
 

Ice2Cool

New Member
Joined
Sep 24, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you Akuini, I will test that out and let you know.
 

Forum statistics

Threads
1,147,453
Messages
5,741,214
Members
423,649
Latest member
steel1968

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top