How to enter and search for multiple different values???

[Guybrush Threepwood]

Hi everyone.
Hope you are all well

So I've embarked on a journey to build a small list (up to 1,000 records) that other users can add to and then filter for various attributes to find what they want.

Pretty simple so far...but:
I need a list of attributes that can be added to each record in the list and then the user needs to be able to search and filter for any one of those attributes.

For example:
Record 1 has attributes of A, B and C
Record 2 has attributes of B and D
Record 3 has attributes of A

I need the user to be able to do a search and filter for attribute A and Record 1 and 3 will show. Search for attribute D and record 2 will show, etc.

Any ideas as to if there are functions in Excel that make this possible? I started off thinking this would be easy, until I realised after creating a separate column for each attribute you can't do a search/filter across all columns (to my knowledge), even in a pivot table.

Many thanks.

 

[cmowla]

Hi.

This is simple, but only because I guess I have been doing this for a while.

The following example sheet corresponds to what you are describing (as well as some VBA code that I will provide to accompany it).

Blank.xlsb
	A	B	C	D	E	F	G	H	I	J
1	Records	Criteria	Column3	Column4	Column5	Column6	Filter Column		Unfilter	d
2	Record 1	a b c					filter
3	Record 2	b d					3
4	Record 3	a					filter
5	Record 4	a e					filter
6	Record 5	f c					filter
7	Record 6	d a					1
8	Record 7	g a					filter
9	Record 8	h c					filter
10	Record 9	d e f					1
Sheet5

Cell Formulas
Range	Formula
G2:G10	G2	=IFERROR(SEARCH($J$1,[@Criteria]),"filter")

1. Convert your table to an Excel Table Object (if it isn't already) by selecting the entire table's contents, pressing Ctrl T, and then following the prompts and click OK.
   
   
2. Insert an extra column for which you will name the header Filter Column (verbatim . . . just as in the image above).
   
   
3. Copy the formula that's in the image above and paste it in the first cell beneath the cell that you typed in Filter Column, but instead of @Criteria in:
   

   =IFERROR(SEARCH($J$1,[@Criteria]),"filter")

   You would put whatever the @columnName is for the column with the criteria. In addition, instead of $J$1, you would put the cell address of the cell you choose to put the yellow (search bar) cell. So if the search bar cell is in Cell M1, you would put $M$1 in place of $J$1 in the above formula, for example.
   
   
4. Carry down the formula to all 1000 rows of your table of the Filter Column only.
   
   
5. Right click on the sheet tab name
   
   
   
   
   
6. Click "View Code"
   
   
7. Copy the code in the code block below and paste in inside it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Cells.Count > 1 Then Exit Sub
If Replace(Target.Address, "$", "") = "I1" Then
    Cancel = True
    Call Unfilter_Table
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Replace(Target.Address, "$", "") = "J1" Then Call Filter_Table
End Sub

There are two things you need to change in the above code. I1 (the cell that you will make an Unfilter button . . . achieved with a double click on that cell) and J1 (the cell that you will type in the criteria in . . . and as soon as you're done, the table will automatically filter).

Once you have made those changes, copy the code below into a standard VBA code module.

Option Explicit

Sub Unfilter_Table()
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
End Sub

Sub Filter_Table()

Dim headerOfFilterColumn As String
headerOfFilterColumn = "Filter Column"

Dim columnNumber As Integer

Dim filterPhrase As String
filterPhrase = "filter"

Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects(1)

Dim rng As Range
Set rng = tbl.DataBodyRange

Dim i As Integer
For i = 1 To rng.Columns.Count
    If rng(0, i).Value = headerOfFilterColumn Then
        columnNumber = i + rng.Column - 1
        Exit For
    End If
Next i

tbl.Range.AutoFilter Field:=columnNumber, Criteria1:="<>" & filterPhrase

End Sub

• Once you are done all steps, you can HIDE the filter column of the table. (Right click on the column letter heading and click "Hide".)
  
  
• When you want to save the Workbook, save it with file extension ".xlsb" instead of ".xlsx".
  
  
• IMPORTANT: This code assumes that you have just ONE Excel Table in your sheet.

If you have any questions, ask away!

Chris

 

[Guybrush Threepwood]

Thanks so much for your help Chris.
One query: If the attributes for each record are individually listed in separate columns how would I cover these columns in the text search formula and VBA?
Eg, columns might be Attribute 1, Attribute 2, Attribute 3, etc.

My plan is to have users select each different attribute from a "list", and where there are multiple attributes for the record they will enter each one individually in the different columns as above.

Thank you.

 

[cmowla]

If that's the case, you would want to use this formula instead. (All of the VBA code still applies.)

Blank.xlsb
	A	B	C	D	E	F	G	H	I	J
1	Records	Criteria 1	Criteria 2	Criteria 3	Criteria 4	Criteria 5	Filter Column		Unfilter	c
2	Record 1	a	b	c
3	Record 2	b	d				Filter
4	Record 3	a					Filter
5	Record 4	a	e				Filter
6	Record 5	f	c
7	Record 6	d	a				Filter
8	Record 7	g	a				Filter
9	Record 8	h	c
10	Record 9	d	e	f			Filter
Table Filter 2

Cell Formulas
Range	Formula
G2:G10	G2	=IF(AND(IFERROR(MATCH($J$1,B2:F2,0),"Filter")="Filter",$J$1<>""),"Filter","")

But as I was playing with this again, I think it would be more user-friendly if I have it clear the value in the yellow search box when you double click on the Unfilter "cell button". So you do need to use the modified version of the following sub to achieve this effect, where you have another J1 to replace with the specific cell address for the search bar.

Sub Unfilter_Table()
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Application.EnableEvents = False
Range("J1").Value = ""
Application.EnableEvents = True
End Sub

 

[Peter_SSs]

Any ideas as to if there are functions in Excel that make this possible?

That could depend on what version of Excel that you are using. I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also be very useful to see just how your particular data is set up and where/how the user is choosing the attributes they are interested in. Perhaps you could consider using the following (with dummy, but representative, sample data):
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Guybrush Threepwood]

MrExcel has a tool called “XL2BB”

Thanks Peter. Have updated my account details, although I'm working on this spreadsheet through work and can't add the XL2BB add-in unfortunately.

If that's the case, you would want to use this formula instead.

Thanks Chris. I tried running the VBA and encountered some problems which I believe are work restrictions related to using macros.
I have however found a formula that seems to work except for one small problem. This is the formula:

=FILTER(Register!$A$6:$P$1000 &"",(Register!$C$6:$C$1000="National")+(Register!$C$6:$C$1000=Location_Search),(Register!$F$6:$F$1000=Barrier_Search)+(Register!$G$6:$G$1000=Barrier_Search),"No Result"

So basically, it's filtering from data entered in another worksheet (Register) based on values the user entered in the "Location_Search" and "Barrier_Search" fields.
My stumbling block at this point is trying to understand whether you can group different Filter parameters together. So essentially the code might look something like this:

=FILTER(Register!$A$6:$P$1000 &"",AND((Register!$C$6:$C$1000="National")+(Register!$C$6:$C$1000=Location_Search),(Register!$F$6:$F$1000=Barrier_Search)+(Register!$G$6:$G$1000=Barrier_Search)),"No Result")

So I'm trying to get the FILTER to show "of the first two parameters one of them must be met, AND of the second two parameters one of them must be met". Any ideas if this can be done? (obviously I can't get the "AND" function to work).

Hope that makes sense
Thanks.

 

[Peter_SSs]

Have updated my account details

Thanks for doing that.

I'm working on this spreadsheet through work and can't add the XL2BB add-in

Then another (not quite so good) option is to make up a small sample file with dummy data that shows us the layout and requirements, copy/paste directly from each of the sheets into your post, make it clear what columns and rows are shown and what the sheet names are, and explain the requirement in relation to that sample data.

 

[cmowla]

Thanks Chris. I tried running the VBA and encountered some problems which I believe are work restrictions related to using macros.

Just to make sure, did they explicitly tell you that you are not allowed to, or you tried but couldn't get the code to run? Because if it's the latter, in order to run VBA code, you have to change the default settings (which has VBA code disabled by default):

File->Options->Trust Center -> Trust Center Settings -> Macro Settings. The Window you should see after following all of those navigation steps should be the following. If the one that's bubbled in the image below is the one that is bubbled in in your window, you need to bubble in the one I have circled in red. Then click OK.

But if it is indeed the case that you are not allowed to do the above at work (they explicitly told you not to), then there is no one better than @Peter_SSs to answer your question about the Filter function. Just honor his request so that he can help you. But based on how I coded the spreadsheet, I don't see how you can use a formula to achieve the same result. There would need to be an input table and the table (which is the result of the formula) as the display table. But if you must, you must. (But please be sure to make absolutely certain that you can't run the VBA code I wrote!)