PDA

View Full Version : Excel 2002 filter query



FoxyMX
17-03-2005, 12:28 PM
I have an Excel 2002 worksheet on which I enter attendance records. One column has the following formula in each cell:

=COUNTIF(R2:AM2,"Yes")

This returns the number of times "Yes" appears in the adjacent cells corresponding with the number of times a person has attended events for that year.

What I want to do is filter out the 0 (zero, nil) entries so that those people who have not attended that year do not get shown in the worksheet. A reverse type of Auto-filter would do the job, ie specify it to hide those 0 entries but I can't figure out how to do this.

I would do a sort but the stick in the works is that I require the names to be in alphabetical order, with surname first (column E) and first name next (column D).

As I am far from being an Excel guru can anyone please explain how I can hide the entries with 0 in column Q?

rad_s4
17-03-2005, 01:44 PM
From the Autofilter drop down on the column containing the 0's, select Custom then on the lefthand side select "does not equal" and on the righthand side enter "0" (without the quotes) then click OK.
All entries other than 0 should be shown.

Alternatively, Sort the data via the 0,s column then select all the 0 rows then right ckick and select Hide to hide the rows, then re-sort on the names column.

HTH

FoxyMX
17-03-2005, 02:07 PM
From the Autofilter drop down on the column containing the 0's, select Custom then on the lefthand side select "does not equal" and on the righthand side enter "0" (without the quotes) then click OK.
All entries other than 0 should be shown.
Once again, absolutely brilliant! :thumbs:

Don't know how I missed the "custom" option in there but I am so used to using auto-filter for other purposes I missed it entirely. :p


Alternatively, Sort the data via the 0,s column then select all the 0 rows then right ckick and select Hide to hide the rows, then re-sort on the names column.
I was about to try that option but thought it might get a bit messy so I am really pleased that the auto-filter does the job.

Many thanks once again. :)