Sleeper: Help With Userform Search Box

[Bradbro]

Hi, I'd like to create a userform box in Excel that will look in two columns. The box will pop up and users will type a name in the box and click a find button. I need the code for searching in the main username column, but I also want it to look in the Sounds Like column too so that if they type John it will also pull up all the Jonathan's or Jon's from the Sounds Like column where they are x-referenced. This search will basically be performing the same search as Auto-Filter>Custom search using the criteria "contains" but it will be looking in two columns.

The goal here is to pinpoint a callers info in the shortest amount of time. Often you'll get a call like "this is John from DC." Well they don't quite realize there are 8 John's and 3 Jon's in that office, so you type John, it pulls up all of the possibles, then when you ask for the last name you've got it on the screen and you're ready to go and you didn't have to try to spell that crazy last name.

[johnske]

Hi Bradbro,

welcome to VBAX

Doing a search in Excel is fairly straight-forward, but the criteria "sounds like" that you've given is native to MS Word and I don't think it can be used in Excel.

You can use Excels native find (in your example) to find john, jon, jonathan etc. by just using "jo" as the criteria for your search, but this will also pull up johannes, joan, joe, joe-anne etc.

Let us know if this approach would be suitable and it won't take long to write the code for you.

Regards,
John



PS: Is it necessary to use a userform for this? You could also use an inputbox - as in this example:


Option Explicit

Sub FindName()
Dim Cell As Range, FirstAddress$, Aname$
Aname = InputBox("What name are you lookin for?", "Name?")
If Aname = Empty Then Exit Sub
With Range("A1:B500") '<< set your own range here
Set Cell = .Find(Aname, LookIn:=xlValues, searchorder:=xlByRows, _
LookAt:=xlPart, MatchCase:=False)
If Not Cell Is Nothing Then '<< if there's something in the given range
FirstAddress = Cell.Address '<< (bookmark)
Do 'Do whatever you want below, here's an example...
Range("C65536").End(xlUp).Offset(1, 0) = Cell.Address & " (" & Cell.Value & ")"
'look for any other names
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End If
End With
End Sub

[mvidas]

Hi BradBro,

I created a sample userform here, see if this suits your needs. Let me know if this is what you're looking for, and what else you may want added to it.

Matt

[Aaron Blood]

J-Walk used to have a soundex search example on his website...

The soundex logic is what you need for "sounds-like" searching.

[mvidas]

Aaron,

Thats a good idea, I had never seen that soundex search before. I decided to take out the padded zeros from the search term's soundex result, so that "john" would match both "jon" and "jonathan" (only matched "jon" with the padded zeros).
Can you think of any improvements for this (see attached)?

Matt

[Killian]

I don't know about "sounds like". Sounds like hard work...

If you use the MatchEntry features of a combobox then as the user types the letters of the name in, the match will build. To make it a little easier and also deal with the "sounds like" scenario, a list box could be used. If it is sorted by criteria like the name or location, the user should be able to select a match from the list pretty quickly.
I've attached an example of what I mean. The user can select what to sort by and this criteria is also used for the entry match as it's typed.
If you have a large dataset, though, the sorting and re-poulation of the list box may be too slow for you.

[johnske]

Hi Aaron,

Didn't know that 'Soundex' one, so I downloaded and checked it out. - Interesting - One thing I noted though is that if you put "jon" as the search criteria, it comes up with "john", "johnny" and such-like, but completely misses out on "johnathan" and "johnatan". It also pulls up names like 'jan" and "jim".

This is a variation of what I had above that's more comprehensive. It finds matches for the first two letters of the name given (or one if you just type in the 1st letter), so johnathan and johnatan are also included (but jan and jim are excluded if you use the first two letters) - for comparison, the names list in the attachment is the same as the downloaded soundex list...


Sub FindName()
Dim Cell As Range, FirstAddress$, Aname$
Columns(3).ClearContents
Aname = InputBox("What name are you lookin for?", "Name?")
If Aname = Empty Then Exit Sub
Aname = Left(Aname, 2)
With Range(Rows(1), Rows(65536).End(xlUp)) '<< set your own range here
Set Cell = .Find(Aname & "*", LookIn:=xlValues, searchorder:=xlByRows, _
LookAt:=xlWhole, MatchCase:=False)
If Not Cell Is Nothing Then '<< if there's something in the given range
FirstAddress = Cell.Address '<< (bookmark)
Do 'Do whatever you want below, here's an example...
Range("C65536").End(xlUp).Offset(1, 0) = Cell.Address & " (" & Cell.Value & ")"
'look for any other names
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End If
End With
End Sub


If we now do a similar search in another column for another identifier such as surname or location and only consider matches that appear on the same row, this narrows the search down considerably, and if those "double-matches" are then presented in a list-box you could go directly to the entry with a click or double-click...

[Zack Barresse]

John,

I would only think about changing one line of your code ..


Set Cell = .Find(Aname & "*", LookIn:=xlValues, searchorder:=xlByRows, _
LookAt:=xlWhole, MatchCase:=False)
Change to ..

Set Cell = .Find("*" & Aname & "*", LookIn:=xlValues, searchorder:=xlByRows, _
LookAt:=xlWhole, MatchCase:=False)
This will be a match for the front side as well. You could also use the LookAt:=xlPart property of the Find method (would be my first instinct).

Jonske's article on the Find method can be found in our new Articles section (http://www.vbaexpress.com/forum/articles.php).

[mdmackillop]

A suggestion, as I don't have time to code anything.
Do two searches, the first based on the first letters eg Jo....
The second to find any sequence later in the string eg Smith, Jo....
List the results sequentially in the Userform.

[johnske]

A suggestion, as I don't have time to code anything.
Do two searches, the first based on the first letters eg Jo....
The second to find any sequence later in the string eg Smith, Jo....
List the results sequentially in the Userform.


Hi Bradbro,

This does two searches (in column A and column B) and lists any results that are approximate matches for the first and last name on the worksheet. You can also put something in the 3rd column (such as an address) to pin the choices down even further.

Have a look at the attachment, if this does something similar to what you need, it can then be cleaned up and a multi-col list box on a userform created to display the results:


Option Explicit
Option Compare Text

Sub FindName()
Dim Cell As Range, FirstAddress$, Aname$, SurName$, N&
Range("D1:F65536").ClearContents
Aname = InputBox("What first name are you lookin for?", "First Name?")
SurName = InputBox("What surname name are you lookin for?", "SurName?")
Aname = Left(Aname, 2)
SurName = Left(SurName, 2)
N = 0
With Range(Columns(1).Rows(1), Columns(1).Rows(65536).End(xlUp))
Set Cell = .Find(Aname & "*", LookIn:=xlValues, searchorder:=xlByRows, _
LookAt:=xlWhole, MatchCase:=False)
If Not Cell Is Nothing Then '<< if there's something in the given range
FirstAddress = Cell.Address '<< (bookmark)
Do
If Cell.Offset(0, 1).Value Like SurName & "*" Then
Range("D65536").End(xlUp).Offset(1, 0) = Cell.Address & _
" (" & Cell.Value & ")"
Range("E65536").End(xlUp).Offset(1, 0) = Cell.Address & _
" (" & Cell.Offset(0, 1).Value & ")"
Range("F65536").End(xlUp).Offset(1, 0) = Cell.Address & _
" (" & Cell.Offset(0, 2).Value & ")"
N = N + 1
End If
'look for any other names
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End If
End With
If N = 0 Then MsgBox "Sorry, there are no matches"
End Sub

(Note that in the names in the attachment, there won't be many matches found. To get more matches, try just typing the initials for the first and last names. - With a real list of names there may be many (say) "Smiths" to match the 1st name)

[Bradbro]

Hi all,
Thanks for all the input so far. I typed a long reply earlier today and it went into the ether. So here goes again:
I've tested the above approaches (except the last one, haven't tried it yet), and I think I need to describe what I'm after a little better. Right now in colum A is a heading "Username" (no quotes) which has last, first in the cells, then there is a heading in column F "Sounds Like" (no quotes either), the cells in this column contain all the iterations of similar sounding names and also phonetic variations on how other names could possibly be spelled if you tried to guess the spelling.
So an example would look like
Username | PC Info | Sounds Like
Smith, John | PC1100545 | John, Jonathan, Jon, Johnny, Johnnie
Jones, Jonathan | PC11200545 |John, Jonathan, Jon, Johnny, Johnnie

Currently if you select Data>Filter>Auto-Filter and click the drop-down on Username
and click (custom) you get the Custom AutoFilter box. On the left you click the drop-down and select "contains" and on the right you type in the name. The result you get is all the rows containing John. This search is perfect, except I also want it to do the searching in the Sounds Like column as well so that if the caller says "this is Johnny from the Vegas office" you will still pull up all the rows containing some form of John. You then ask for their last name and you'll spot his name right away in the alpha listing of all the possible combinations of John. I just picture this box sitting open and as you get a call you don't have to fumble through multiple clicks or searches to get their PC Info. Is something like this do-able? Thanks.

[johnske]

Hi Bradbro,

So, basically you will use auto-filter most of the time and you just want to be able to click onto column F, scroll down to find John, Jonathan, Jon, Johnny, Johnnie in your own "sounds like" column. And then pull up all rows where there is a match for "Jo" in column A? (as in Jones, Johnathan or Smith, John)...

Seems to me that auto-filter should do that for you - if you click on column F/custom/top RH drop-down button/OK - doesn't that do what you want?

John

[Bradbro]

Not really, because there will be times when you are typing the last name in the search, then you would have to set the Sounds Like column to (all) first to unfilter from the last search you performed, then click the drop down on Username to bring up the custom autofilter search, set it to contains and type in the name. I want to avoid all that and just build a little box that already does this, then you type the name, hit go and your there. The idea is to minimize clicks. Is there no way to concatenate the Username and Sounds Like columns in one search?

[johnske]

Not really, because there will be times when you are typing the last name in the search, then you would have to set the Sounds Like column to (all) first to unfilter from the last search you performed, then click the drop down on Username to bring up the custom autofilter search, set it to contains and type in the name. I want to avoid all that and just build a little box that already does this, then you type the name, hit go and your there. The idea is to minimize clicks. Is there no way to concatenate the Username and Sounds Like columns in one search?


First, you don't have to type in the name... in 'Custom', to the right of where you type in the name, there is a drop-down button on the RHS of it that lists everything in that column. You only need to select what you want from the drop-down list and then OK. If there are no empty columns between A and F all of the rows with variations on "John" should come up with Username to the left.

(You can do almost anything you like with VBA, it's just a matter of first seeing exactly what's required and next, seeing whether or not an in-built function can be easily adapted to do what you want. For instance - you could have another column with "Username" and "Sounds Like" concatenated and use auto-filter on that)...

John :)

[Bradbro]

The problem with the drop-down theory in AutoFilter is that there are 2500 names in the list (needless to say this isn't the John company, there are all kinds of names). When someone calls I need to pinpoint them right away without scrolling down through a huge list. How do you concatenate the Username and the Sounds Like column?

Okay, I'm getting a little closer now. I did a formula to combine Username and Sounds Like into one column. Now using Custom Auto-Filter delivers what I'm looking for. I would still like a little help making it easier to bring up the Custom Auto-Filter box, ready to go with the contains criteria. I tried recording a macro to bring up the box, but it won't let you finish recording the macro without clicking okay (then the box goes away and nothing happens). Can anyone modify the code below to make the Custom Autofilter box stay open and ready to type in the search name? Thanks, and sorry for being such a newb...


Sub Lookupperson()
' Lookupperson Macro
' Macro recorded 3/1/2005 by Bradbro
' Keyboard Shortcut: Ctrl+l
Selection.AutoFilter Field:=6, Criteria1:="=**", Operator:=xlAnd
End Sub