How can I place a search box in my spreadsheet that searches a range of cells containing comma separated keywords?

[jeremybritz]

The question is in the title, but I've attached a screenshot\edit to sort of get at what I'm trying to do. Let me know if you need any clarification on what I'm trying to accomplish!

 

[lrobbo314]

This code assumes that your comma separated data is in column A beginning in A1. I added an activeX textbox object to the sheet. The code for the textbox:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
    Dim fStr As String:     fStr = TextBox1.Value
    Dim AR() As Variant:    AR = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
    Dim AL As Object:       Set AL = CreateObject("System.Collections.ArrayList")
    Dim SP() As String
    
    For i = 1 To UBound(AR)
        SP = Split(AR(i, 1), ",")
        For j = LBound(SP) To UBound(SP)
            If SP(j) = fStr Then AL.Add i
        Next j
    Next i
    
    Range("C1").Resize(AL.Count, 1).Value = Application.Transpose(AL.toarray)
End If
End Sub

The code fires when you hit 'Enter'. The rows that have the value from the textbox will be output to column C.

I am sure that a fair bit of this code will need to be adjusted since you didn't give a clear example of where your data is or what you want to do with the found results.

 

[jeremybritz]

Ahh, sorry I realize my screenshot doesn't really make as much sense as I initially had hoped.

What I'm trying to do (as per my screenshot) is have cell B1 be the searchbox (I guess this could be the activeX textbox). Once you search for an object, the categories that that object falls under would display in cell B2. So, if you search for apple it would display Fruits, and Still-Life Items (preferably on a new line) in B2, as apple is a keyword in both of these categories.


Hope this makes a bit more sense. Thanks so much for the help!!

This code assumes that your comma separated data is in column A beginning in A1. I added an activeX textbox object to the sheet. The code for the textbox:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
    Dim fStr As String:     fStr = TextBox1.Value
    Dim AR() As Variant:    AR = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
    Dim AL As Object:       Set AL = CreateObject("System.Collections.ArrayList")
    Dim SP() As String
    
    For i = 1 To UBound(AR)
        SP = Split(AR(i, 1), ",")
        For j = LBound(SP) To UBound(SP)
            If SP(j) = fStr Then AL.Add i
        Next j
    Next i
    
    Range("C1").Resize(AL.Count, 1).Value = Application.Transpose(AL.toarray)
End If
End Sub

The code fires when you hit 'Enter'. The rows that have the value from the textbox will be output to column C.

I am sure that a fair bit of this code will need to be adjusted since you didn't give a clear example of where your data is or what you want to do with the found results.

 

[Peter_SSs]

Welcome to the MrExcel board!

Here is one way.
1. Make your data (A5:B9 in your example) into a formal table (if not already). That is, select the data then on the menus Insert -> Table -> My table has headers -> OK
This will mean the method should continue to automatically adjust if new data is added to that table.

2. Select B2 and format as 'Wrap Text'

3. Enter the formula shown in B2. Note that the table name needs to match your actual table name and the items in [] need to match your actual table headings.
This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

4. Enter your value in B1.

Excel Workbook
	A	B
1		apple
2		FruitsStill-Life Items
3
4
5	Name of Category	Keywords
6	Fruits	apple, banana, orange,dragonfruit
7	Cars	beetle, malibu, camaro
8	Inedible Objects	table, chair, glasses
9	Still-Life Items	apple, table
10
List Items

BTW, for the future, you will get more help & faster if you provide your sample data in a form that can be copied into our own spreadsheet for testing. My signature block below has more help on that.

 

[jeremybritz]

Thanks so much! This works awesome. Is there any way to format the resulting data? Currently it all displays on one line, I would like to possible display it on a separate line for each result.

Thanks again!!!!

Welcome to the MrExcel board!

Here is one way.
1. Make your data (A5:B9 in your example) into a formal table (if not already). That is, select the data then on the menus Insert -> Table -> My table has headers -> OK
This will mean the method should continue to automatically adjust if new data is added to that table.

2. Select B2 and format as 'Wrap Text'

3. Enter the formula shown in B2. Note that the table name needs to match your actual table name and the items in [] need to match your actual table headings.
This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

4. Enter your value in B1.

List Items

	A	B
1		apple
2		Fruits Still-Life Items
3
4
5	Name of Category	Keywords
6	Fruits	apple, banana, orange,dragonfruit
7	Cars	beetle, malibu, camaro
8	Inedible Objects	table, chair, glasses
9	Still-Life Items	apple, table
10

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:217px;"><col style="width:251px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 {=TEXTJOIN(CHAR(10),TRUE,IF(ISNUMBER(SEARCH(", "&B1&", ",", "&Table1[Keywords]&", ")),Table1[Name of Category],""))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


BTW, for the future, you will get more help & faster if you provide your sample data in a form that can be copied into our own spreadsheet for testing. My signature block below has more help on that.

 

[Peter_SSs]

Is there any way to format the resulting data? Currently it all displays on one line, I would like to possible display it on a separate line for each result.

Sounds like you may have skipped step 2? As you can see in my screen shot, the results are shown on separate lines.


BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.

 

[jeremybritz]

Sounds like you may have skipped step 2? As you can see in my screen shot, the results are shown on separate lines.


BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.

Thanks for all the tips, you were right, I didn't have it selected.

Is there any way for users to be able to copy the text after the results are shown?

 

[jeremybritz]

Actually, is there a way to select the text rather then it switching to the function when I click the results? Is there a function that will copy the results as text into a new cell?

 

[lrobbo314]

Sorry about that. Your picture was blocked by my browser. Here's an updated VBA solution. Paste the code below in the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B1"), Target) Is Nothing Then
    Dim fStr As String:     fStr = Target.Value
    Dim AR() As Variant:    AR = Range("A6:B" & Range("A" & Rows.Count).End(xlUp).Row).Value
    Dim AL As Object:       Set AL = CreateObject("System.Collections.ArrayList")
    Dim SP() As String
    
    For i = 1 To UBound(AR)
        SP = Split(AR(i, 2), ", ")
        For j = LBound(SP) To UBound(SP)
            If SP(j) = fStr Then AL.Add AR(i, 1)
        Next j
    Next i
    
    Range("B2").Value = Join(AL.toarray, vbLf)
End If


End Sub

 

[Peter_SSs]

Is there any way for users to be able to copy the text after the results are shown?

Just Copy cell B2 and use Paste Special -> Values where you want the text