Creating a "Search Engine" for a large spreadsheet

[Bruce_B_2002]

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.

 

[klb]

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]

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]

Or, and a **** sight easier, you could use common sense and do what klb suggested .

 

[Bruce_B_2002]

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]

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]

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]

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]

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.