Searching a database with keywords, filtering as you type

wbstadeli

Board Regular
Joined
Mar 11, 2016
Messages
103
Hi all,

Im starting to build a database workbook for our machine shop that has items we order from different vendors/type of product/cost of product/item number ETC.. This will end up having many entries in it. In very simple terms i want to search thru this list with a "Search box" that will auto populate (or filter) AS i type showing only the items that match the "keywords" I type in the search box. What i would really want is that the order of keywords doesn't matter, nor case-sensitive, and it can contain numbers/text and even decimal points, because that is how i want to filter out items. Below is a small example of what im trying to accomplish:

ABCDE
1Product:Diameter:Overall Length:Cost:Item number:
2Endmill.255$37.00EDP9900
3Endmill.3755$45.00EDP7888
4Drill.255$3.00EDP112222

<tbody>
</tbody>

keywords i "search"Result of search of data above
Search Box:endmillrow 2 and row 3
Search Box:endmill .25row 2
Search Box:.25Row 2 and row 4
Search Box:5Row 2, row 3, and row 4

<tbody>
</tbody>

Any ideas? :)
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,920
.
Paste this macro into the Sheet Level module :

Code:
Private Sub TextBox1_Change()
Application.ScreenUpdating = True
 ActiveSheet.ListObjects("SearchTable").Range.AutoFilter Field:=11, _
        Criteria1:="1", Operator:=xlFilterValues
        Application.ScreenUpdating = True
End Sub
See sample workbook : https://www.amazon.com/clouddrive/share/pALlqqv30SsPAom4dsNJidmCho6MATrqPyImH74RcXo

There is also code in a Routine Module (see workbook) for clearing the search.

Cols K & L can be hidden.

If you modify the file, change the Col Headers to :



D
E
F
G
H
3
ABCDE
4
Product:Diameter:Overall Length:Cost:Item number:
5
Endmill
0.25
5
$37.00
EDP9900
6
Endmill
0.375
5
$45.00
EDP7888
7
Drill
0.25
5
$3.00
EDP112222


You'll be able to search anything on the sheet and return the data.

Practice with the download workbook first to see how it runs. Then ... save a copy of the workbook before you begin
making changes. That way you will have a clean copy on your computer to work with again if necessary. Of course
you should hopefully always have the download link here but I can't guarantee it.
 
Last edited:

wbstadeli

Board Regular
Joined
Mar 11, 2016
Messages
103
.
Paste this macro into the Sheet Level module :

Code:
Private Sub TextBox1_Change()
Application.ScreenUpdating = True
 ActiveSheet.ListObjects("SearchTable").Range.AutoFilter Field:=11, _
        Criteria1:="1", Operator:=xlFilterValues
        Application.ScreenUpdating = True
End Sub
See sample workbook : https://www.amazon.com/clouddrive/share/pALlqqv30SsPAom4dsNJidmCho6MATrqPyImH74RcXo

There is also code in a Routine Module (see workbook) for clearing the search.

Cols K & L can be hidden.

If you modify the file, change the Col Headers to :



[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
ABCDE
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
Product:Diameter:Overall Length:Cost:Item number:
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
Endmill
0.25
5
$37.00
EDP9900
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
Endmill
0.375
5
$45.00
EDP7888
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
Drill
0.25
5
$3.00
EDP112222

<tbody>
</tbody>



You'll be able to search anything on the sheet and return the data.

Practice with the download workbook first to see how it runs. Then ... save a copy of the workbook before you begin
making changes. That way you will have a clean copy on your computer to work with again if necessary. Of course
you should hopefully always have the download link here but I can't guarantee it.
Sorry i took so long to get back, i was out of the office with a new baby :) Anyways thank you for your response, this is very cool! So is there a way to improve the search (going off your sample file) so i can type multiple words in the search bar to filter down results more? Example: If i type "honda" i get 3 results, i want to add "mitch" to narrow results down to 1. So the search box would be "Honda mitch". Is this possible?
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,920
.
The answer is No. The macro is designed to filter (search) one column at a time and return the entire row of anything that is found

So you could search "Mitch" and return everything it finds for that term. Or you could search "Honda" and the same result displayed but this time there would be other names included as well.

It is possible to either "hard code" the filtering as is shown in this resource : https://analysistabs.com/vba/filter/multiple-columns/

OR ... you could edit the 'hard coded terms' to reference a text box (as is done with the attached workbook), then you could have two or three different text boxes
used for the search terms. Textbox1 could be for Col A; Textbox2 could be for Col B, etc. etc.
 

Forum statistics

Threads
1,081,994
Messages
5,362,607
Members
400,684
Latest member
Vie

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top