Searching a database with keywords, filtering as you type

[wbstadeli]

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:

	A	B	C	D	E
1	Product:	Diameter:	Overall Length:	Cost:	Item number:
2	Endmill	.25	5	$37.00	EDP9900
3	Endmill	.375	5	$45.00	EDP7888
4	Drill	.25	5	$3.00	EDP112222

<tbody>
</tbody>

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

<tbody>
</tbody>

Any ideas?

 

[Logit]

.
Paste this macro into the Sheet Level module :

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​	A	B	C	D	E
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.

 

[wbstadeli]

.
Paste this macro into the Sheet Level module :

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]​	A	B	C	D	E
[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]

.
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.