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

[Ice2Cool]

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?

 

[Akuini]

@Ice2Cool, welcome to the forum
I have an example of a searchable combobox , see if it suit your requirements.

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:

deCombobox - sheet,1 cell, searchable,dictionary 1.xlsm

Shared with Dropbox

www.dropbox.com

 

[Ice2Cool]

Thank you Akuini, I will test that out and let you know.