Filtering

  • Thread starter Thread starter Freshman
  • Start date Start date
F

Freshman

Dear experts,

My staff created a table with many records. The structure
of the table is: column A - staff names; column B -
furniture no. 1, column C - funriture no. 2, column D -
furniture no. 3 and so forth... The example is listed
below for reference.

Paul 11 12 19 27
Sam 19 27 35 28
Susan 11 35 38 42
Mary 12 19 35 43

My question is, if I want to sort out records with
furniture no. 11 OR 19 or any combinations, what should I
do? What the formula or any function can do? I tried to
use advanced filter function, but I can get some records
bit by bit and not one time filtering.

Please kindly advise. Thanks.
 
You can do this with an Advanced Filter.

Set up a criteria area
1. To make filtering easier, type the values that you want to filter
For example, in cell G1, type 11, in cell H1, type 19
2. In an unused area of the worksheet, leave a blank cell, and in
the cell below, enter a formula that refers to the first row of
data in your table. For example, leave cell J1 blank, and in J2:
=AND(COUNTIF($B2:$E2,$G$1),COUNTIF($B2:$E2,$H$1))

Filter the table
1. Select a cell in the data table
2. Choose Data>Filter>Advanced Filter
3. Choose to Filter in Place
4. For the list range, select your table
5. For the criteria range, select cells J1:J2
6. Click OK
 
With your data as stated in A:D heading sin row 1 data in row 2 onwards, put the
following formula in F2, leaving F1 blank, and then select F1:F2 as your
criteria:-

=SUM(COUNTIF(A2:D2,{11,19}))>0
 
Thanks Ken. I guess the capitalized 'OR' in the question should have
been a clue.
 
Back
Top