Results 1 to 6 of 6

Thread: Not sure if this type of search and display is possible...

  1. #1
    New Member
    Join Date
    Jun 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Not sure if this type of search and display is possible...

    For example, I have the following data set:

    Name Fruit1 Fruit2 Fruit3
    1 Bob Apples Oranges Pears
    2 Jill Oranges Apples Bananas
    3 Biff Apples Oranges Pears
    4 Tad Bananas Pears Peaches
    5 Peg Grapefruit Apples Pears
    6 Bill Bananas Pears Oranges
    7 Don Oranges Oranges Oranges

    I would like to be able to "filter" based on the word "Bananas" and then every row/record in the sheet containing Bananas (irrespective of which column) will be displayed and the rest hidden. In this example, entire rows 2, 4, and 6 would be displayed, the rest hidden. The basic and advanced filters don't seem to work and I haven't found the right macro example that will accomplish this either.

    Any help is appreciated!!!

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,812
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Not sure if this type of search and display is possible...

    Maybe this VBA solution

    Code:
    Sub MM1()
    Dim lr As Long, r As Long
    Rows.EntireRow.Hidden = False
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = 2 To lr
        If Application.WorksheetFunction.CountIf(Range("B" & r & ":D" & r), "Bananas") = 0 Then
            Rows(r).EntireRow.Hidden = True
        End If
    Next r
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    New Member
    Join Date
    Jun 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Not sure if this type of search and display is possible...

    Hello Michael,

    Your example was exactly what I need to get started. Made a few tweaks and it is doing exactly what I needed.

    Many thanks!

  4. #4
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,603
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Not sure if this type of search and display is possible...

    This can be done with Advanced Filter
    If "Name" is in A1
    Leave G1 blank and put the formula =(COUNTIF(B2:D2, "bananas")>0) in G2

    Data Range - A1:D8
    Criteria Range - B1:G2

    will show the rows where "bananas" is in one of columns B:D

  5. #5
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,812
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Not sure if this type of search and display is possible...

    Glad to help....Mikes method is more suited if you don't want to use VBA
    AND
    is simpler as it is a native function of Excel....
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  6. #6
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,603
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Not sure if this type of search and display is possible...

    BIG EDIT: the Criteria Range should be G1:G2

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •