How to filter rows with commom Nº's in any column

[Moti]

Hello,
How is it posible to filter the common Nº that may appear in an
coloumn

Examlpe:

A B C D E F
2 6 8 10 15 20
1 2 5 16 25 30
4 8 12 19 45 48
2 4 15 20 25 28
6 9 19 29 39 49
8 19 25 28 41 45

In this example I want to filter all the rows that have the commo
Nº8
The filtered Result Should be shown as below:

A B C D E F
2 6 8 10 15 20
4 8 12 19 45 48
8 19 25 28 41 45

Thank You
Moti

 

[Debra Dalgleish]

Add a new column to the table, and use it to find the number.

For example, in cell J1, type the number you want to find
in cell G1, enter a heading, e.g. Found
In G2, type the formula: =COUNTIF(A2:F2,$J$1)
Copy the formula down to the last row of data

Or, you could use conditional formatting to highlight the cells that
contain specific numbers. There are instructions here:

http://www.contextures.com/xlCondFormat03.html#Lottery

 

[Don Guillett]

One way. Select a range in a column like a2:a200 then.
Sub filter8()
For Each c In Range("a2:a200") 'Selection
x = c.Row
If Application.CountIf(Range("a" & x & ":f" & x), 8) < 1 Then _
c.EntireRow.Hidden = True
Next
End Sub

 

[hcj]

Hi Moti,
There may be a better answer from someone, but if not,
using your example, do the following:
1. Select an empty cell near but not in the range of your
data. Place the test value in that cell (e.g. 8).
2. Use column G to hold a counter for each row. In each
G cell, insert the countif function where the range is
the data in that row and the criteria is the test value
cell in absolute reference.
3. Apply autofilter to the data. Filter column G for any
value greater than 0 (i.e. there's at least one occurence
of the test value in that row)

Hope this helps you. hcj

 

[Domenic]

Hi Moti,

Here's another way,

1) leave cell 14 blank
2) cell 15 =COUNTIF(A2:F2,8)>0
3) select the range of cells you want sorted
4) Data > Filter > Advanced Filter
5) use $A$14:$A$15 as your criteria
6) click OK

Hope this helps!