Filter when there are several elements per row?

[G809]

Hello, XLS friends!
I would like to figure out how I can create a database of information with a filter to find the right info quickly when there are several elements listed in a single row. For example, I have a list of books, and each has several characteristics:
Book A: Romance, thriller
Book B: Comedy, romance
Book C: Satire, technical
Book D: Thriller
Book E: Comedy, Childrens, Romance

Name of the book (e.g. A) is column 1, and the characteristic is column 2. I want to be able to choose "romance" from the drop down and report back A, B, and E; or, I would like to be able to choose "Satire" and report back C. How do I enter the data into the rows so that this works? Currently I just have them separated by semi-colons but it just gets jumbled together as one characteristic (e.g. "comedy,romance").

Thank you!

 

[Weaver]

If you're using autofilter, pick 'custom' and choose 'contains' in the top left drop box, then to the right of this, type the characteristic you're after.

HTH

 

[G809]

Thanks! I understand how the filter works, but I am trying to make this a document that is usable by others in my organization, most of whom are not excel savvy. Is there a way to set this as the default filter? I don't want them to have to figure out how to use the custom filter each time, or they won't use the doc. Ideally, there could be a filter at the top that says "Romance", etc., and they click it and it outputs everything which contains "Romance". Any suggestions for this?

 

[Weaver]

If you wanted to go this route, I think you'd need some vb.

Here's what you need to do:

1. Set up a cell with a validation list in it. You can type this straight in using the validation wizard or create a named range (my prefered method). Assume for the example that the cell is A1, your data starts in A3 (header row = 3) and the column you need to filter is column B

Right click the tab, choose 'view code' and paste the following in.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        On Error GoTo safeExit
        Application.EnableEvents = False
        With Range("A3")
            .AutoFilter
            If Target.Value <> "All" Then .AutoFilter field:=2, Criteria1:="*" & Target.Value & "*"
        End With
    End If
safeExit:
    Application.EnableEvents = True
End Sub

Then when someone changes the value in the validation list, the sheet gets resorted. If you include the word "All" in your validation list, this will remove the filter.

HTH

 

[Weaver]

This is a slight refinement that keeps the filter active when showing all, rather than removing it completely

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        On Error GoTo safeExit
        Application.EnableEvents = False
        With Range("A3")
            If ActiveSheet.AutoFilterMode Then
                .AutoFilter Field:=2
            Else
                .AutoFilter
            End If
            If Target.Value <> "All" Then .AutoFilter Field:=2, Criteria1:="*" & Target.Value & "*"
        End With
    End If
safeExit:
    Application.EnableEvents = True
End Sub