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

[pharrcide]

For example, I have the following data set:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Name[/TD]
[TD]Fruit1[/TD]
[TD]Fruit2[/TD]
[TD]Fruit3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bob[/TD]
[TD]Apples[/TD]
[TD]Oranges[/TD]
[TD]Pears[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jill[/TD]
[TD]Oranges[/TD]
[TD]Apples[/TD]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Biff[/TD]
[TD]Apples[/TD]
[TD]Oranges[/TD]
[TD]Pears[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Tad[/TD]
[TD]Bananas[/TD]
[TD]Pears[/TD]
[TD]Peaches[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Peg[/TD]
[TD]Grapefruit[/TD]
[TD]Apples[/TD]
[TD]Pears[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Bill[/TD]
[TD]Bananas[/TD]
[TD]Pears[/TD]
[TD]Oranges[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Don[/TD]
[TD]Oranges[/TD]
[TD]Oranges[/TD]
[TD]Oranges[/TD]
[/TR]
</tbody>[/TABLE]

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!!!

 

[Michael M]

Maybe this VBA solution

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

 

[pharrcide]

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!

 

[mikerickson]

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

 

[Michael M]

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....

 

[mikerickson]

BIG EDIT: the Criteria Range should be G1:G2