Search Entire Table?

[Necroscope]

Hi, I have a fairly standard table containing columns with office names, addresses, site codes, server numbers and contact names for each site.

I want to be able to have a search box that I can type a word into and have matching results returned. I can do this using formula to search one column (the office name field) but I want to be able to search ALL columns because - for example - if I were to type "London" in to a search field, I'd want all the London offices to be listed but some of these are mostly known by other names like "Victoria House" or "Alexandra Park". However, if the search looked at the address column too, it would find everything. Also, searching all columns would also help me if I knew a server number but didn't know the office that has that server. I could type that in and it would return the office name/address details too...

Ideally, I'd like a little mini table to show under the search box and have Office Name > Address > Site Code > Server Number > Contact name details.

I've no preference on whether this is done via formula, VBA, a Userform or something like that. The database is on one sheet and my current search attempt is on a separate sheet but I am happy to go with whatever is easier, if this is possible in the first place of course... Can anyone please help?

Thanks.

 

[James006]

Hi,

You can easily perform this task with Filter ...

See Debra's explanations :https://www.contextures.com/xladvfilter01_2003.html

Hope this will help

 

[Necroscope]

Hi James.

Many thanks for the reply. I had looked at something like this but it won't work for my situation. What I didn't add in my original post is that I've already got a separate sheet set up with an ActiveX combo box that people who use this spreadsheet can simply select an office from the dropdown (or start typing in to the box) and the various details relating to that office appear in boxes below. This is done just using a fairly simple VLookup formula.

But our senior manager has asked that - because of the "London" issue mentioned in my original post - I need to make this 'simpler' for users and just have a single search box that will look in all fields, not just the office name one...!

 

[James006]

Hi,

In order to Find All Instances with VBA ... you can take a look at following link:

https://www.thespreadsheetguru.com/the-code-vault/2014/4/21/find-all-instances-with-vba

Hope this will help :wink:

 

[Necroscope]

Hi James.

Thanks again - this VBA highlights matching entries on the table but that's not really helpful for me. Mainly because the table is 400+ rows and about 46 columns so it doesn't really do what I want unfortunately.

 

[Akuini]

Question:
Once an item is found, what would you like to happen?
Copy the entire row to another sheet (the mini table)?

 

[Necroscope]

Hi Akuini.

Ideally I'd like to display the entire row but I don't mind whether that's copied elsewhere or just displayed below the search box etc... As long as someone can type into a search field and matching results are displayed, that would be great... So, if I were to type in 12345 as a server ID, for example, the result would show:

Office name Address Server ID Contact
Manchester HQ 1 Any Street, Manchester 12345 Big Jim!

Same result whether I typed in "Manchester" or "HQ" or "Big Jim" etc...

Hope that makes sense.

 

[Akuini]

Actually copying the entire row will be simpler to code.
Another question:
Do you want to search as a whole or part of a cell?
If as a part then searching for London will include "London", "London City" etc.

 

[Necroscope]

The entire row is fine - anything that makes things simpler is fine with me... And the search would need to be part of a cell so that "London" would return London and London City etc...

Many thanks.

 

[Akuini]

The entire row is fine - anything that makes things simpler is fine with me... And the search would need to be part of a cell so that "London" would return London and London City etc...
.

Ok, try this:
Note:
1. In Sheet1 is the data, Sheet2 is for the result, change to suit (in the code).
2. In Sheet2 you need 2 activex control: textbox & command button.
3. In Sheet2 you need to manually copy paste the headers (from sheet1) to row 2.

Here's the code:
(Code in module1)

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] toSearch()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1085320-search-entire-table.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range, c [COLOR=Royalblue]As[/COLOR] Range, q [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] a [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], b [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]


Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
Sheets([COLOR=brown]"Sheet2"[/COLOR]).Activate
a = Cells.Find([COLOR=brown]"*"[/COLOR], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
b = Cells.Find([COLOR=brown]"*"[/COLOR], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Range(Cells([COLOR=crimson]3[/COLOR], [COLOR=brown]"A"[/COLOR]), Cells(a + [COLOR=crimson]1[/COLOR], b)).ClearContents

[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Sheet1"[/COLOR])
a = .Cells.Find([COLOR=brown]"*"[/COLOR], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
b = .Cells.Find([COLOR=brown]"*"[/COLOR], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

[COLOR=Royalblue]Set[/COLOR] r = .Range(.Cells([COLOR=crimson]2[/COLOR], [COLOR=brown]"A"[/COLOR]), .Cells(a, b))

[COLOR=Royalblue]Set[/COLOR] c = r.Find(what:=ActiveSheet.TextBox1.Text, LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=[COLOR=Royalblue]False[/COLOR], SearchFormat:=[COLOR=Royalblue]False[/COLOR])
[COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] c [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    sAddress = c.Address
    [COLOR=Royalblue]Do[/COLOR]
        [COLOR=Royalblue]If[/COLOR] q [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
            [COLOR=Royalblue]Set[/COLOR] q = .Range(.Cells(c.Row, [COLOR=brown]"A"[/COLOR]), .Cells(c.Row, b))
            [COLOR=Royalblue]Else[/COLOR]
            [COLOR=Royalblue]Set[/COLOR] q = Union(q, .Range(.Cells(c.Row, [COLOR=brown]"A"[/COLOR]), .Cells(c.Row, b)))
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
   
       [COLOR=Royalblue]Set[/COLOR] c = r.FindNext(c)
    
    [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]While[/COLOR] c.Address <> sAddress
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] q [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    q.Copy
    Range([COLOR=brown]"A3"[/COLOR]).PasteSpecial xlPasteValues
    [COLOR=Royalblue]Else[/COLOR]
    MsgBox [COLOR=brown]"No match"[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

Code for commandbutton to call the above code:
(Code in sheet2 code window)

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] CommandButton1_Click()
[COLOR=Royalblue]Call[/COLOR] Module1.toSearch
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]

Here's an example:

https://www.dropbox.com/s/6ulhytvrl2ax523/1085320-search-entire-table.xlsm?dl=0