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

jeremybritz

New Member
Joined
Jul 25, 2019
Messages
9
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

Well-known Member
Joined
Jul 14, 2008
Messages
2,588
Office Version
365, 2019, 2016
Platform
Windows
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:

Code:
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

New Member
Joined
Jul 25, 2019
Messages
9
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:

Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,237
Office Version
365
Platform
Windows
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
AB
1apple
2
3
4
5Name of CategoryKeywords
6Fruitsapple, banana, orange,dragonfruit
7Carsbeetle, malibu, camaro
8Inedible Objectstable, chair, glasses
9Still-Life Itemsapple, 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.
 
Last edited:

jeremybritz

New Member
Joined
Jul 25, 2019
Messages
9
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

AB
1 apple
2 Fruits
Still-Life Items
3
4
5Name of CategoryKeywords
6Fruitsapple, banana, orange,dragonfruit
7Carsbeetle, malibu, camaro
8Inedible Objectstable, chair, glasses
9Still-Life Itemsapple, table
10

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

Spreadsheet Formulas
CellFormula
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,237
Office Version
365
Platform
Windows
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.
 
Last edited:

jeremybritz

New Member
Joined
Jul 25, 2019
Messages
9
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

New Member
Joined
Jul 25, 2019
Messages
9
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

Well-known Member
Joined
Jul 14, 2008
Messages
2,588
Office Version
365, 2019, 2016
Platform
Windows
Sorry about that. Your picture was blocked by my browser. Here's an updated VBA solution. Paste the code below in the worksheet module.

Code:
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
 

Forum statistics

Threads
1,081,990
Messages
5,362,584
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top