Creating a "Search Engine" for a large spreadsheet

Bruce_B_2002

New Member
Joined
Oct 17, 2002
Messages
3
At our office we have a large Excel spreadsheet (as in 39,281 rows) which lists graphics files available on our server. Each column represents their filename, file extension, their subject, the project they're related to, their path on the server, etc.

I need to create a "search engine" in Excel that would allow a user to enter anywhere from one to six criteria (each criterion has its own separate entry field) in order to find the desired graphic file from this enormous spreadsheet. These six criteria match column categories from the spreadsheet. The more criteria entered, the more refined the search. For example, let's say a user is looking for a picture of an Army M1 tank, preferably in a *.bmp format. The user could simply enter “M1” in the Subject field, which would then search for any files and formats having that designation. However, if the same user enters “Tank” in the Type field, “bmp” in the File Extension field, “M1” in the Subject field, and then clicks a "send" button, the search engine will only return a more refined list of *.bmp files of Army M1 tank images and show each file’s path.

I hope this makes sense. I'm just wondering if there's a macro out there on the internet that is closely related to this type of function and "search bar" interface. I haven't been able to find one. I would welcome ideas on where I might find related macro instruction as well. Anyone's help or guidance here would be very much appreciated. Thanks.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

klb

Well-known Member
Joined
Apr 3, 2002
Messages
821
Have you tried applying an autofilter to the file.

Select all of your data got to the menu and select Data, Filer, AutoFilter.

Good luck
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
A bit difficult to answer this 'straight off the bat' as it could become rather involved, but one way would be to have a combobox (or multiselect listbox even) from the toolbox, not forms menu, at the top of each of your columns or fields.

A piece of code (below, edited slightly) from John Walkenbach's 'Excel 2002 Power Programming With VBA' could then be used to fill each of the comboboxes with unique items from each of your fields. The user would then select an item(s) from one or more of the comboboxes and press a command button. The command button would then trigger an autofilter on each of the columns where an item was selected, thus hiding entries which didn't fit their criteria.

As a starting point, try recording a macro of yourself applying an autofilter to your sheet and selecting different items in each of the columns. This should give you a flavour of what you should be doing. Then, add your comboboxes, name your ranges and add the code below to each of them. Sorry I can't be more specific, but a step-by-step guide to this would cover several A4 sheets and might not fit in with what you want.

<pre>
Public Sub RemoveDuplicates1()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection

On Error Resume Next
For Each Cell In Range("Data")
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0

For Each Item In NoDupes
Sheet1.ComboBox1.AddItem Item
Next Item

End Sub
</pre>
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
Or, and a **** sight easier, you could use common sense and do what klb suggested :biggrin:.
 

Bruce_B_2002

New Member
Joined
Oct 17, 2002
Messages
3

ADVERTISEMENT

Thanks very much for the responses. I've assembled the ComboBox and linked it with the code suggested above, but receive a compile error: "Method or Data Member Not Found" for the ".ComboBox1." portion of the code. Is there some type of linking problem with my ComboBox and the macro?
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
Check the combobox is from the Toolbox (not the Forms menu) and is actually on the sheet named Sheet1 in the VBA explorer 'Microsoft Excel Objects' folder (you'll have something like Sheet1(Sheetname) where Sheetname is whatever you have called the sheet).

Also, klb's AutoFilter suggestion is a far easier way of doing this and will give the same results.
 

Bruce_B_2002

New Member
Joined
Oct 17, 2002
Messages
3

ADVERTISEMENT

I appreciate the Autofilter suggestion (and all suggestions), but unfortunately, Autofilter's limit is 250 items...well short of covering the 39,000+ rows of information on my spreadsheet. That's why I'm looking to construct my own google-like search engine, where one enters data in each "search bar" for each column--looking for like entries--as opposed to an enormous drop-down list for each column. I know...I must be crazy, but thanks again for your assistance.
 

klb

Well-known Member
Joined
Apr 3, 2002
Messages
821
I don't know about any limitations but I am able to use autofilter on a file with almost 50,000 records and have not had a problem. In many of the columns there are way more than 250 different entries. Of course with that many records, I end up transferring data to Access for better analysis.

Am using Access97 on nt 4.0 machine.
 

pablofsix

New Member
Joined
Feb 20, 2013
Messages
2
Re: Creating a "Search Engine" for a large spreadsheet

i found a good solution for this problem, look at this excelminiapps+search engine
I don't know about any limitations but I am able to use autofilter on a file with almost 50,000 records and have not had a problem. In many of the columns there are way more than 250 different entries. Of course with that many records, I end up transferring data to Access for better analysis.

Am using Access97 on nt 4.0 machine.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

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