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!

 

[jeremybritz]

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

Since the results are multi-line is there any way to allow users to select separate lines? Currently it only allows you to select all of the text.

 

[jeremybritz]

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

Hey there,

I came into work and we are running Office Professional Plus 2016, it appears the TEXTJOIN function is not supported by this version? Is this something you are familiar with?

 

[jeremybritz]

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

I got this working for the most part, however instead of displaying the cell contents it simply displays the cell number. (column C displays 1, 4 , 5 rather than Fruits, Still-Life Objects, and Red Objects.

See screenshot

 

[Peter_SSs]

Hey there,

I came into work and we are running Office Professional Plus 2016, it appears the TEXTJOIN function is not supported by this version? Is this something you are familiar with?

If you don't have the TEXTJOIN function then I would also use a vba solution. However, since you also want to be able to select an individual item from the results, I'm wondering if you really want a multi-line value all in cell B2? Selecting an individual line from the formula bar could be cumbersome, particularly if there are quite a few values in the result. Why not just filter the original table and then you can just select any visible item from the 'Name of Category' column?

Consider this one-liner Worksheet_Change code. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1. (Note you would need to remove any existing Worksheet_Change code)
3. Close the Visual Basic window & test by entering/clearing values in cell B1.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B1")) Is Nothing Then ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:="*" & Range("B1").Text & "*"
End Sub

Here is my sheet after I have entered "table" in B1

Excel Workbook
	A	B
1		table
2
3
4
5	Name of Category	Keywords
8	Inedible Objects	table, chair, glasses
9	Still-Life Items	apple, table
10
List Items

 

[jeremybritz]

Thanks for the help with this!

 

[Peter_SSs]

Thanks for the help with this!

You are welcome. Hope you ended up with something satisfactory.