How do I filter multiple columns at once?

[Guest]

In my spreadsheet I have the courses that each uni student is taking for the
current year. Each student (by row) can take up to 8 courses a year so there
are 8 different columns. At certain points throughout the year, I will need
to draw up a classlist for example of everyone in the 2nd year who is taking
modernism. Therefore, I will need to filter all 8 columns at once to provide
all the rows containg 'modernism'. However, because Excel provides additive
filtering, once I have filtered column 1 for 'modernism', I have already
probably hidden info I need from the other 7 columns and the next column I
filter will only filter from the previous filter put in place.
So, I would like to know how to filter all 8 columns for a particular course
simultaenously because filtering 1 column at a time will only be additive and
will hide info I need from the other columns.

 

[Roger Whitehead]

Could you not have a column for each course by name? This way (for example)
column E is headed 'Modernism', and the entries in the rows below contain a
flag - 'Y' might do - though you may have to develop a coding system
according to your requirements.

 

[Hans Knudsen]

In my spreadsheet I have the courses that each uni student is taking for
the
current year. Each student (by row) can take up to 8 courses a year so
there
are 8 different columns. At certain points throughout the year, I will
need
to draw up a classlist for example of everyone in the 2nd year who is
taking
modernism. Therefore, I will need to filter all 8 columns at once to
provide
all the rows containg 'modernism'. However, because Excel provides
additive
filtering, once I have filtered column 1 for 'modernism', I have already
probably hidden info I need from the other 7 columns and the next column I
filter will only filter from the previous filter put in place.
So, I would like to know how to filter all 8 columns for a particular
course
simultaenously because filtering 1 column at a time will only be additive
and
will hide info I need from the other columns.

You might want to try the following:
If you have "courses" in for example B2:I100 try entering in cell J2 the
array formula:
=OR(B2:I2="modernism"). (Remember to hold down the Ctrl and Shift keys while
pressing Enter. If entered correct the formula should look like this:
{=OR(B2:I2="modernism")}. Copy the formula down through J100. You should get
TRUE if there is at least one "modernism", otherwise FALSE. Now you can
filter on column J.

Regards
Hans Knudsen

 

[Guest]

Could you not have a column for each course by name? This way (for example)
column E is headed 'Modernism', and the entries in the rows below contain a
flag - 'Y' might do - though you may have to develop a coding system
according to your requirements.


I couldn't really do that because there are around 50-60 different courses to choose from and we'd rather not have 50-60 different columns. The plan is to have a drop down list in each cell of these 8 columns of all the courses available so I can then enter the correct one. This is why, courses will not necessarily be attributed to only 1 specific column.

Cheers

 

[Guest]

Hi,

Filter by using advanced criteria

The Advanced Filter command on the Data menu lets you use complex criteria
(criteria: Conditions you specify to limit which records are included in the
result set of a query. For example, the following criterion selects records
for which the value for the Order Amount field is greater than 30,000: Order
Amount > 30000.) to filter a range, but it works differently from the
AutoFilter command in several important ways.

It displays the Advanced Filter dialog box instead of the Custom AutoFilter
dialog box.

You do not type the complex criteria in the Advanced Filter dialog box as
you do in the Custom AutoFilter dialog box. Rather, you type the complex
criteria in a criteria range on the worksheet and above the range you want to
filter. Excel uses the separate criteria range in the Advanced Filter dialog
box as the source for the complex criteria.

Although you can filter a range in place, like the AutoFilter command, the
Advanced Filter command does not display drop-down lists for the columns.

1. Insert at least three blank rows above the range that can be used as a
criteria range. The criteria range must have column labels. Make sure there
is at least one blank row between the criteria values and the range.

2. In the rows below the column labels, type the criteria you want to match.

3. Click a cell in the range.
4. On the Data menu, point to Filter, and then click Advanced Filter.
5. To filter the range by hiding rows that don't match your criteria, click
Filter the list, in-place.

To filter the range by copying rows that match your criteria to another area
of the worksheet, click Copy to another location, click in the Copy to box,
and then click the upper-left corner of the area where you want to paste the
rows.

6. In the Criteria range box, enter the reference for the criteria range,
including the criteria labels.

To move the Advanced Filter dialog box out of the way temporarily while you
select the criteria range, click Collapse Dialog .

7. To change how the data is filtered, change the values in the criteria
range and filter the data again.

Challa Prabhu

 

[Guest]

Hi,

Filter by using advanced criteria

The Advanced Filter command on the Data menu lets you use complex criteria
(criteria: Conditions you specify to limit which records are included in the
result set of a query. For example, the following criterion selects records
for which the value for the Order Amount field is greater than 30,000: Order
Amount > 30000.) to filter a range, but it works differently from the
AutoFilter command in several important ways.

It displays the Advanced Filter dialog box instead of the Custom AutoFilter
dialog box.

You do not type the complex criteria in the Advanced Filter dialog box as
you do in the Custom AutoFilter dialog box. Rather, you type the complex
criteria in a criteria range on the worksheet and above the range you want to
filter. Excel uses the separate criteria range in the Advanced Filter dialog
box as the source for the complex criteria.

Although you can filter a range in place, like the AutoFilter command, the
Advanced Filter command does not display drop-down lists for the columns.

1. Insert at least three blank rows above the range that can be used as a
criteria range. The criteria range must have column labels. Make sure there
is at least one blank row between the criteria values and the range.

2. In the rows below the column labels, type the criteria you want to match.

3. Click a cell in the range.
4. On the Data menu, point to Filter, and then click Advanced Filter.
5. To filter the range by hiding rows that don't match your criteria, click
Filter the list, in-place.

To filter the range by copying rows that match your criteria to another area
of the worksheet, click Copy to another location, click in the Copy to box,
and then click the upper-left corner of the area where you want to paste the
rows.

6. In the Criteria range box, enter the reference for the criteria range,
including the criteria labels.

To move the Advanced Filter dialog box out of the way temporarily while you
select the criteria range, click Collapse Dialog .

7. To change how the data is filtered, change the values in the criteria
range and filter the data again.

Challa Prabhu

Hi
I tried using the advanced filter function earlier and it didn't seem to do
what I needed but I was probably not doing it right. I will try again
cheers