Excel: How copy all rows that have a given column entry?

OK, Roger, great. Thanks a lot for all your help.
-Mick

"Roger Govier" wrote:

Hi Mick

Glad you got it sorted.

In answer to your question, if there is another question on another
topic, you would be best to post as a new topic, because
a) The topic is different
b) You will have the chance of far more people answering you, rather
than answers just from me
c) the thread is getting rather long!!!

I will try to look out for your new posting, but there are lots of
people far more able than myself, who will probably pick it up and give
you the answer.

--
Regards

Roger Govier


"Motown Mick" wrote in message
...
Thanks, Roger! That was exactly what I needed to do.

I had another question on a different topic. Should I post it
separately,
or can I continue it on this discussion thread with you?

Mick

"Roger Govier" wrote:

Hi Mick
If that is the case, then you need
=OR(AF20, AI20, AL20).

--
Regards

Roger Govier


"Motown Mick" wrote in message
...
Dear Roger,

It does not appear that the numeric procedure you outlined in your
message
of 6/7/2007, 2.22AM achieved the desired result. Rather than
flitering for
the rows that have at least one entry greater than zero, it appears
to
have
filtered for the rows that are completely free of non-zero entries.

Rather than create an additional column and run the risk of
disturbing
my
previous analyses, I filled in a column way off to the right, AZ,
which I
labelled "Filter" in AZ1, and wrote in AZ2 the fomula =AND(AF20,
AI20,
AL20). I clicked check and saw that "FALSE" appeared in AZ2. I
dragged the
formula down the range of data, to AZ68, where the data ended.

"FALSE" appeared in every cell, barring one; this was the cell
corresponding
to a row that had an entry greater than zero in each of the above
columns I
perfromed this operation on; AF, AI and AL.

This cannot have performed the desired operation because, by visual
inspection alone, I was able to see that column AF had at least a
dozen or so
entries greater than zero in it. If the operation you outlined had
performed
the desired result, there would have been at least a dozen
appearances
of
"TRUE" in column AZ.

Please allow me to repeat for emphasis: I am NOT trying to filter
for
rows
that are COMPLETELY free of non-zero entries. I AM trying to
filter
for the
rows that contain AT LEAST ONE non-zero entry.

If you know of a procedure that can accomplish this, please kindly
share
your knowledge with me. Otherwise, the visual inspection procedure
I
have
already outlined is satisfactory for my needs for the time being.

Thank you for your assistance.

Mick

"Roger Govier" wrote:

Hi Mick

No, the filter will deal will most things you throw at it.
A custom condition of Greater than and 0 will work fine.

Equally, the last formula I provided will work, giving just one
"new"
column to filter on.

Just go ahead and try it. You will soon find how versatile and
useful
the function is.
--
Regards

Roger Govier


"Motown Mick" wrote in
message
...
Dear Roger,

Yes, sorry I had to throw multiple posts at you, I didn't even
see
my
own
post.

Before I try any of these procedures you are suggesting, I
wanted
to
be sure
that they are designed to accomplish the task I am trying to do.
They
sound
like they may be designed to filter for the rows that are
COMPLETELY
free of
non-zero entries.

This is not what I am trying to do. I am trying to filter for
the
rows that
contain AT LEAST ONE entry greater than zero.

Please let me know if you think the last numeric procedure you
outlined will
accomplish that task. If it does, it would probably be ideal
for
what
I am
trying to accomplish.

If it does not, can you think of any other procedures that might
accomplish
this task?

Thank you.

Mick

"Roger Govier" wrote:

I think there must have been a problem with the server
yesterday,
as
you
are the second person not to see a reply I posted.
Here is what I said

Each Filter is independent.
Say you have filters applied to columns A through G, so they
all
have
the filter button showing.
Lets assume that you are interested in filtering based upon
Column
A,
D
and F

Use the dropdown on column A, to select non blanks. You will
now
see
a
subset of rows, where all of column A contains values.
Whilst that filter is in place, apply a filter to column D, and
you
list
will be smaller, with no blanks appearing in A or D.
Now apply a filter to column D and you will have all of the
rows
where
there are no blanks in A or D or F.

The filter is therefore cumulative.
Clicking any of the filtered columns, and selecting All, just
removes
the filter for that column - the others would remain, unless
you
selected All on each in turn.

The option DataFilterShow All or using the Show All icon
dragged
to
your toolbar, removes all filters off all columns in one go and
you
are
back to your full list of data again.

If you are saying you want to filter for all three columns in
one
go,
then you would need to add an additional column - lets say
column
H.
In
cell H2 enter
=AND(ISBLANK(A2),ISBLANK(D2),ISBLANK(F2))
Copy this down column H and it will return either TRUE or FALSE
Filter on this column for FALSE and you will have all of the
rows
which
do not have blank entries in columns A, D and F.

Note. If your data is numeric and you are looking for values
greater
than 0, make the formula
=AND(A20,D20,F20) and filter for TRUE.


--
Regards

Roger Govier


"Motown Mick" wrote in
message
...
Dear Roger:

I was able to create the Show All and Autofilter icons this
time.
Thanks.

Just an aside; the adjascent columns within the range that I
am
working on
that I don't wish to filter aren't blank.

I highlighted the entire range of columns I wish to perform
the
filtering
operation on. Then I did DataFilterAutofilter and saw that
it
was
only
these columns that had the dropdown arrow on them. I clicked
the
dropdown on
the primary column and applied the filtering operation on it.
I
saw
that its
arrow turned blue.

It does not appear that the filtering operation was performed
on
any
of the
other columns.

Then I clicked "Show All" and it undid the filtering
operation
on
the
primary columns. Then I went and applied the operation to
the
the
auxilliary
columns as well.

I think I will be OK with performing the operation as I have
described
above. I now know a quick and efficient way to undo the
filter
and
apply it
to the other columns I am interested in looking at. By
looking
at
the
primary column after applying the filter to the auxilliary
columns,