Need to find only rows with Y in any of 5 columns ??

[aware073]

Hey guys I have this spreadsheet where in columns N, Q, T, W, and Z there are either X's or Y's. I want to eventually only see columns that have a Y in them in any of those 5 columns and hide any that do not. I tried a simple =IF(N2=Q2=T2=W2=Z2, "HIDE","OK") in a new column so that afterwards I could sort by that and hide all the OK's, but that formula wont work for some reason. Any suggestions?

Excel 2010
	A	B	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Plnt	Material	2100	BOH		5800	BOH		2500	BOH		2300	BOH		2700	BOH
2	2100	20011416	0	1.25	Y	0	0	X	0	0	X	0	0	X	0	0	X
3	2100	20012183	631.48	4.268	X	0	0	X	76	7	X	140.99	1.4	X	0	0	X
4	2100	20000020	21	5	X	0	0	X	0	0	X	0	0	X	0	0	X
5	2100	20000025	2	2	X	0	0	X	0	0	X	0	0	X	0	0	X
6	2100	20000066	54	3	X	4	0	X	0	0	X	0	0	X	0	0	X
7	2100	20000115	230	18	X	218	4	X	103	12	X	0	0	X	0	0	X
8	2100	20000129	237	22	X	125	10	X	41	9	X	66	12	X	0	0	X
9	2100	20000168	6	3	X	0	0	X	0	0	X	0	0	X	0	0	X
10	2100	20000175	8	3	X	2	2	X	0	0	X	0	0	X	0	0	X
11	2100	20000176	17	3	X	12	2	X	0	0	X	0	0	X	0	0	X
12	2100	20000177	11	3	X	10	1	X	0	0	X	0	0	X	0	0	X
13	2100	20000181	12	3	X	16	1	X	0	0	X	0	0	X	0	0	X
14	2100	20000274	5	2	X	0	0	X	0	0	X	1	1	X	2	3	Y
15	2100	20000295	23	2	X	1	0	X	0	0	X	6	1	X	1	2	Y
16	2100	20000297	15	2	X	0	0	X	0	0	X	1	2	Y	0	1	Y
17	2100	20000352	1	1	X	0	0	X	0	0	X	1	2	Y	0	2	Y
18	2100	20000446	4	2	X	0	0	X	9	7	X	3	2	X	3	3	X
19	2100	20000449	4	2	X	4	0	X	0	1	Y	0	0	X	0	0	X
20	2100	20000453	6	2	X	0	0	X	0	0	X	0	4	Y	0	13	Y
21	2100	20000497	31	5	X	6	0	X	0	0	X	0	0	X	0	0	X
22	2100	20000567	2	2	X	5	0	X	4	2	X	1	1	X	0	2	Y
23	2100	20000570	4	2	X	0	0	X	0	0	X	0	0	X	0	0	X
24	2100	20000571	5	2	X	0	0	X	0	0	X	0	0	X	0	0	X
25	2100	20000583	2	2	X	0	0	X	0	0	X	1	1	X	1	3	Y
Over Max

 

[Gingertrees]

To clarify your terminology: are you looking to hide ROWS or COLUMNS? From your description, it seems like you're actually looking for a row-by-row thing.
This would hide rows 2, 14:17, 19:20, 22, and 25.
Otherwise, in your example, only column Q would be hidden.

 

[revcanon]

try this formula instead:

=IF(OR($N2="Y",$Q2="Y",$T2="Y",$W2="Y",$Z2="Y"),"OK","HIDE")

 

[aware073]

To clarify your terminology: are you looking to hide ROWS or COLUMNS? From your description, it seems like you're actually looking for a row-by-row thing.
This would hide rows 2, 14:17, 19:20, 22, and 25.
Otherwise, in your example, only column Q would be hidden.

Sorry yes rows is what I meant!!

 

[aware073]

Perfect! Thanks you so much.

 

[Gingertrees]

revcanon's formula works, just drag it down over the region you want.
If it's a huge region, it might be faster to do this via VBA. Put this code in a standard module in the visual basic editor (Insert/Module), and it will show up in your Macros list on the Developer tab:

Sub countys()
Application.ScreenUpdating = False


Dim Lastrow As Integer
Dim Teststg As String
Lastrow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row


For i = 2 To Lastrow
     Teststf = Range("N" & i) & Range("Q" & i) & Range("T" & i) & Range("W" & i) & Range("Z" & i) '//concatenates
     Range("AA" & i).Select
        Selection = InStr(1, Teststf, "y") '//counts the "y"s
        If Selection = 0 Then
            Selection.EntireRow.Hidden = True '//when a row has no Ys in target columns, this hides that row
        End If
Next i
Application.ScreenUpdating = True


End Sub