Search bar that searches through multiple columns in another worksheet

[Elaina]

I have a large inventory database (almost 8,000 rows by 38 columns) generated by querying many different spreadsheets. The queries are merged into one giant table that I would like to search from. Each row contains many columns of information about the piece of equipment that someone might need to know. However, there could be duplicate equipment from the different spreadsheets I'm querying, but one was described in one way in a specific column, and the other person described it slightly differently but with similar key words in a different column.

I've found VBA code for a search bar that looks for the results on a different sheet and I've found VBA code using option boxes to search by different column titles, but I'm not experienced enough with code language to be able to combine the concepts myself.

• I'm looking for a search bar which will search through a large amount of data I have on a separate sheet.
• I would also like to be able to do a search on different columns' data, or even multiple columns at a time would be ideal. Maybe a combo box with columns titles to select which rows to search in or just a general search for any word or number matches in the entire data range could work as well. (It could serve my purposes well even if I could just search in one desired column at a time, but it would function better the more columns I'm able to search at once.)
• Then I would like the rows of data that match to be listed below the search bar.
• Also I want there to be an asterisks in the search so that if I search 1234, then 12345 will show up as well as AB1234.
• Bonus: Being able to search for more than one search item at a time. (For instance, "1200HP motor", motor would be found in one column and 1200HP would be found in another.)

Good result: I'm looking for "motors" within the "equipment" column on the separate sheet titled "All" and all of the rows with motors in the equipment column would show up below.

Better result: I'm looking for the part number "22367A-1000" in the columns "Part # OEM" and "Part # Vendor" on the separate sheet "All" and all of the rows that contain those numbers and letters in a row would show up below my search.


Is this possible? Or is there a better way to get the result I'm looking for? Let me know if I need to provide more information.

 

[Elaina]

Anyone have any ideas or any suggestions for another course of action?

 

[mumps]

Copy and paste this macro into the worksheet code module for a blank sheet. You can name the sheet anything you want. Do the following: right click the tab for this sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter your search value in cell A1 and exit the cell. This macro will search the "All" sheet in all the columns for the value you entered. It will find the value even in a cell that contains other characters. Please note that this macro assumes that there will always be data in every row of column A in the "All" sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim foundVal As Range
    Dim sAddr As String
    With Sheets("All").UsedRange
        Set foundVal = .Find(Target, LookIn:=xlValues, lookat:=xlPart)
        If Not foundVal Is Nothing Then
            sAddr = foundVal.Address
            Do
                foundVal.EntireRow.Copy Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                Set foundVal = .FindNext(foundVal)
            Loop While foundVal.Address <> sAddr
            sAddr = ""
        End If
    End With
    Set foundVal = Nothing
    Application.ScreenUpdating = True
End Sub

The length of time it will take to run the macro will depend on the size of the source data.

 

[Elaina]

Awesome this works really well!! Thank you so much!

However is there anyway that the "results" could clear when I go to search for something else?

 

[Elaina]

Also...could it be added in the code that if the cell is blank then it won't search for anything, because that freezes up the processing pretty badly.

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim foundVal As Range
    Dim sAddr As String
    If Target = "" Then Exit Sub
    With Sheets("All").UsedRange
        Set foundVal = .Find(Target, LookIn:=xlValues, lookat:=xlPart)
        If Not foundVal Is Nothing Then
            sAddr = foundVal.Address
            Do
                foundVal.EntireRow.Copy Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                Set foundVal = .FindNext(foundVal)
            Loop While foundVal.Address <> sAddr
            sAddr = ""
        End If
    End With
    Target.ClearContents
    Set foundVal = Nothing
    Application.ScreenUpdating = True
End Sub

 

[Elaina]

Yes that works.

But now something strange is happening. When I tried to apply any formatting to any cell, if it takes more than one click I can not access any menu item. For instance I cannot put an outsides border on any cell as that requires me to click the drop down menu and then select something else, OR if I want to apply conditional formatting, I cannot because it requires my to click on the dropdown and then click which type I want to select. Does that have anything to do with this macro?

 

[mumps]

This macro is triggered only by a change in cell A1. It shouldn't affect any formatting you are trying to do. Are you using any other macros? I think that it would be much easier to follow if I could see how your data is organized. Perhaps you could upload a copy of your file to a free site such as www.box.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you are trying to do referring to specific cells and worksheets.

 

[Elaina]

I have scrubbed the document of a lot of data because the information in confidential however this will show you how everything is set up.

I need to be able to type a word of number in cell B2 on sheet "Search" and the results start showing on cell A5. That is working, however when I go to type something else into B2 I need everything from cell A5 to the right and below to be cleared so the new results will show when I type something else into B2. I would also like what I've just searched for to remain in cell B2 until I search for something else. The table "All" is generated by an appended query, in case you need this information. I'm not sure if you can access the table without powerquery enabled on your ribbon, however I'm not sure.

Thanks,


Here is the link: https://www.dropbox.com/s/4gwbpfadf1wgtqp/Critical Spares Query.xlsm?dl=0

 

[Elaina]

If the data on "All" is not accessible, I will copy paste the table and get rid of the query in this version. I don't think it should make a difference as far as the macro is concerned.