Filtering

P

Peter Lareau

I have a worksheet in which the first column contains the
names of 135 individuals. The next 9 columns (each of
which represents a unique date for a meeting of an
organization) indicate whether the individual in column 1
was present ("P") or absent ("A") on the date represented
by that column.

I want to filter the data to display only those rows that
represent individuals who did not have perfect attendance
at all nine meetings; i.e. those individuals who have at
least one "A" in the nine columns adjacent to their names.

Any help will be greatly appreciated.
 
E

Earl Kiosterud

Peter,

You want only rows with P in all 9 columns. That could be done with an
autofilter, by filtering for P in each, but would not be convenient. You
could also set up an Advanced filter with a criteria range that specified P
in each field, but that takes time to set up each time, though you can
automate it by recording a macro. A third option is to set up a helper
column:

=IF(AND(B2:J2="A"),"A","")

Enter it as an array formula (Ctrl-Shift-Enter). Now use Autofilter, and
look for "A".
 
M

Mike A

In the cell to the right of your last meeting day, type:

=COUNTIF(A1:D1,"=A")


Copy this down, then use Auto filter on this column and use the criteria
'is greater than 0'.


Mike Argy
Custom Office solutions and
Windows/UNIX applications
 
Top