Do you know a quick and efficient method on how to mark or flag somehow, and
copy to the clipboard, all the rows in a worksheet that have an entry in a
given column, or set of columns, that conforms to some criterion (greater
than zero, in my case)?

How about DataFilterAutofilter?

Filter on your criterion then copy the results to the clipboard.


Gord Dibben MS Excel MVP

On Thu, 31 May 2007 10:46:02 -0700, Motown Mick
wrote:

Do you know a quick and efficient method on how to mark or flag somehow, and
copy to the clipboard, all the rows in a worksheet that have an entry in a
given column, or set of columns, that conforms to some criterion (greater
than zero, in my case)?

Dear Gord,

Thank you for your prompt reply. Unfortunately, I think I need a more
detailed response. I am not sure how to "filter on [my] criterion". I did
DataFilterAutofilter. A check appeared beside the Autofilter indicator,
but nothing else happened. Is there any more you can add to your reply that
might provide me the missing information I need to accomplish my task?

Mick

"Gord Dibben" wrote:

How about DataFilterAutofilter?

Filter on your criterion then copy the results to the clipboard.


Gord Dibben MS Excel MVP

On Thu, 31 May 2007 10:46:02 -0700, Motown Mick
wrote:

Do you know a quick and efficient method on how to mark or flag somehow, and
copy to the clipboard, all the rows in a worksheet that have an entry in a
given column, or set of columns, that conforms to some criterion (greater
than zero, in my case)?

Hi

Assuming your data has headers in row 1.
Mark the block of data.
DatafilterAutofilter
Choose the dropdown on the column required and choose Custom
Set the parameters to Greater than and 0

--
Regards

Roger Govier


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

Thank you for your prompt reply. Unfortunately, I think I need a more
detailed response. I am not sure how to "filter on [my] criterion".
I did
DataFilterAutofilter. A check appeared beside the Autofilter
indicator,
but nothing else happened. Is there any more you can add to your
reply that
might provide me the missing information I need to accomplish my task?

Mick

"Gord Dibben" wrote:

How about DataFilterAutofilter?

Filter on your criterion then copy the results to the clipboard.


Gord Dibben MS Excel MVP

On Thu, 31 May 2007 10:46:02 -0700, Motown Mick
wrote:

Do you know a quick and efficient method on how to mark or flag
somehow, and
copy to the clipboard, all the rows in a worksheet that have an
entry in a
given column, or set of columns, that conforms to some criterion
(greater
than zero, in my case)?

Thanks Roger.

That's about it, although OP does mention multiple columns which might give a
wrinkle.

Wait and see.


Gord

On Fri, 1 Jun 2007 00:37:09 +0100, "Roger Govier"
wrote:

Hi

Assuming your data has headers in row 1.
Mark the block of data.
DatafilterAutofilter
Choose the dropdown on the column required and choose Custom
Set the parameters to Greater than and 0

Dear Roger,

Thanks! I think I've got it now; and also by reading about "filter" on
Excel's built-in help.

I just clicked a cell in one of the relevant columns, did
DataFilterAutofilter, then clicked on the arrow that appears in each column
when you do that, clicked "custom", saw that pop-up menu, did "greater than"
"0", and that seems to have done the work for me.

Only two remaining questions:

1. I'm not sure what you mean by "mark the block of data". Rather than
checking for values greater than zero in one column, it would be more ideal
for my purposes if I could filter for values greater than zero in more than
one column simultaneously. If I understand you correctly, how could I "mark
the block of data" I wish to perform the analysis on so as to include more
than one column? In other words, how do I filter for data greater than zero
in column A, B, and C, and filter out any row that has a value greater than
zero in at least one of those columns?

2. Once I have done this, how do I get the worksheet back to normal--i.e.,
the way it was before I did the filtering? I've been closing the document
without saving, and then re-opening it every time I want to play around with
the filtering device so as not to destroy the previous results. What a
nuisance!

Mick

"Roger Govier" wrote:

Hi

Assuming your data has headers in row 1.
Mark the block of data.
DatafilterAutofilter
Choose the dropdown on the column required and choose Custom
Set the parameters to Greater than and 0

--
Regards

Roger Govier


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

Thank you for your prompt reply. Unfortunately, I think I need a more
detailed response. I am not sure how to "filter on [my] criterion".
I did
DataFilterAutofilter. A check appeared beside the Autofilter
indicator,
but nothing else happened. Is there any more you can add to your
reply that
might provide me the missing information I need to accomplish my task?

Mick

"Gord Dibben" wrote:

How about DataFilterAutofilter?

Filter on your criterion then copy the results to the clipboard.


Gord Dibben MS Excel MVP

On Thu, 31 May 2007 10:46:02 -0700, Motown Mick
wrote:

Do you know a quick and efficient method on how to mark or flag
somehow, and
copy to the clipboard, all the rows in a worksheet that have an
entry in a
given column, or set of columns, that conforms to some criterion
(greater
than zero, in my case)?

Hi Mick

What I meant was select the complete range of your data before applying
Autofilter, then you will get the dropdowns on each column within the
range.
You can apply filters to as many columns as you wish, each time it will
reduce the selection to those rows that match each of the criteria you
have applied.

Clicking the dropdown on the column again, and selecting All will remove
the filter from that particular column.
DataFilterAutofilter again, will remove all filters - it is a toggle,
either to switch it on or off but this removes the dropdowns altogether.

I have dragged a Show All command to my toolbar, which I can click to
remove all filters at once, whilst leaving the dropdowns in place.

ViewToolbarsCustomiseCommandsData drag the Show All to your
toolbar. It appears as Text only, but whilst you are still in Customise
mode, you can right click on the Text Show All and choose an icon if you
wish.

--
Regards

Roger Govier


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

Thanks! I think I've got it now; and also by reading about "filter"
on
Excel's built-in help.

I just clicked a cell in one of the relevant columns, did
DataFilterAutofilter, then clicked on the arrow that appears in each
column
when you do that, clicked "custom", saw that pop-up menu, did
"greater than"
"0", and that seems to have done the work for me.

Only two remaining questions:

1. I'm not sure what you mean by "mark the block of data". Rather
than
checking for values greater than zero in one column, it would be more
ideal
for my purposes if I could filter for values greater than zero in more
than
one column simultaneously. If I understand you correctly, how could I
"mark
the block of data" I wish to perform the analysis on so as to include
more
than one column? In other words, how do I filter for data greater
than zero
in column A, B, and C, and filter out any row that has a value greater
than
zero in at least one of those columns?

2. Once I have done this, how do I get the worksheet back to
normal--i.e.,
the way it was before I did the filtering? I've been closing the
document
without saving, and then re-opening it every time I want to play
around with
the filtering device so as not to destroy the previous results. What
a
nuisance!

Mick

"Roger Govier" wrote:

Hi

Assuming your data has headers in row 1.
Mark the block of data.
DatafilterAutofilter
Choose the dropdown on the column required and choose Custom
Set the parameters to Greater than and 0

--
Regards

Roger Govier


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

Thank you for your prompt reply. Unfortunately, I think I need a
more
detailed response. I am not sure how to "filter on [my]
criterion".
I did
DataFilterAutofilter. A check appeared beside the Autofilter
indicator,
but nothing else happened. Is there any more you can add to your
reply that
might provide me the missing information I need to accomplish my
task?

Mick

"Gord Dibben" wrote:

How about DataFilterAutofilter?

Filter on your criterion then copy the results to the clipboard.


Gord Dibben MS Excel MVP

On Thu, 31 May 2007 10:46:02 -0700, Motown Mick
wrote:

Do you know a quick and efficient method on how to mark or flag
somehow, and
copy to the clipboard, all the rows in a worksheet that have an
entry in a
given column, or set of columns, that conforms to some criterion
(greater
than zero, in my case)?

Dear Roger,

I tried holding down the control key, and selecting the columns I wanted to
perform the operation on such that they were all highlighted simultaneously;
I had to do it this way because they are non-adjascent columns.

When I did autofilter, I got an error message that said the command I chose
could not be performed with multiple sections, and that I had to select a
single range and try again.

I suppose I could copy all the relevant columns to another location, arrange
them adjascently, and performe the filtering operation on the bunch of them
that way. As long as all the pertinent rows matched, it would produce the
same desired result. If you know of a quicker easier way to perform this
operation on non-adjascent columns, please kindly share your knowledge.

It's doesn't matter that much, though, because by clicking "All" on the
dropdown menu, as you have explained, and playing with the autofilter
function on the other columns, I am able to get the knowledge I am looking
for in a fairly expedient fashion. What's going on is I have three columns
in which I am looking for entries greater than zero. There is one primary
column in which I am looking for entries greater than zero, but I need to
check and make sure that the other two columns don't have entries greater
than zero in rows in which the primary column is blank. Performing the
autofilter on the auxiliary rows, and then checking the primary column for
blank spaces accomplishes that task fairly efficiently, now that I know about
"All" on the dropdown menu, thanks to you.

Your instructions on how to create the "Show All" icon on my toolbar didn't
seem to work for me, but does this just do the same thing as selecting "All"
on the column dropdown menu as I have been doing?

Mick

"Roger Govier" wrote:

Hi Mick

What I meant was select the complete range of your data before applying
Autofilter, then you will get the dropdowns on each column within the
range.
You can apply filters to as many columns as you wish, each time it will
reduce the selection to those rows that match each of the criteria you
have applied.

Clicking the dropdown on the column again, and selecting All will remove
the filter from that particular column.
DataFilterAutofilter again, will remove all filters - it is a toggle,
either to switch it on or off but this removes the dropdowns altogether.

I have dragged a Show All command to my toolbar, which I can click to
remove all filters at once, whilst leaving the dropdowns in place.

ViewToolbarsCustomiseCommandsData drag the Show All to your
toolbar. It appears as Text only, but whilst you are still in Customise
mode, you can right click on the Text Show All and choose an icon if you
wish.

--
Regards

Roger Govier


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

Thanks! I think I've got it now; and also by reading about "filter"
on
Excel's built-in help.

I just clicked a cell in one of the relevant columns, did
DataFilterAutofilter, then clicked on the arrow that appears in each
column
when you do that, clicked "custom", saw that pop-up menu, did
"greater than"
"0", and that seems to have done the work for me.

Only two remaining questions:

1. I'm not sure what you mean by "mark the block of data". Rather
than
checking for values greater than zero in one column, it would be more
ideal
for my purposes if I could filter for values greater than zero in more
than
one column simultaneously. If I understand you correctly, how could I
"mark
the block of data" I wish to perform the analysis on so as to include
more
than one column? In other words, how do I filter for data greater
than zero
in column A, B, and C, and filter out any row that has a value greater
than
zero in at least one of those columns?

2. Once I have done this, how do I get the worksheet back to
normal--i.e.,
the way it was before I did the filtering? I've been closing the
document
without saving, and then re-opening it every time I want to play
around with
the filtering device so as not to destroy the previous results. What
a
nuisance!

Mick

"Roger Govier" wrote:

Hi

Assuming your data has headers in row 1.
Mark the block of data.
DatafilterAutofilter
Choose the dropdown on the column required and choose Custom
Set the parameters to Greater than and 0

--
Regards

Roger Govier


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

Thank you for your prompt reply. Unfortunately, I think I need a
more
detailed response. I am not sure how to "filter on [my]
criterion".
I did
DataFilterAutofilter. A check appeared beside the Autofilter
indicator,
but nothing else happened. Is there any more you can add to your
reply that
might provide me the missing information I need to accomplish my
task?

Mick

"Gord Dibben" wrote:

How about DataFilterAutofilter?

Filter on your criterion then copy the results to the clipboard.


Gord Dibben MS Excel MVP

On Thu, 31 May 2007 10:46:02 -0700, Motown Mick
wrote:

Do you know a quick and efficient method on how to mark or flag
somehow, and
copy to the clipboard, all the rows in a worksheet that have an
entry in a
given column, or set of columns, that conforms to some criterion
(greater
than zero, in my case)?

Hi Mick

I fear I may have mislead you with my instructions.
It does not matter whether there are any blank columns within your data,
or whether there are columns that you don't wish to filter.
I usually mark the complete range then DataFilterautofilter to be sure
that the filter is available on all columns that I wish to use, even if
some are blank within the range. You can click on any cell within your
data and choose DataFilterAutofilter, but if you have any blank
columns, then it will think the table only applies up to the first blank
column, and will not apply the dropdown to other columns beyond that.

Just use the dropdowns on any columns that you wish to filter by.
When a filter is applied, the small downward pointing arrow on the
filter button, turns Blue, but this can sometimes be difficult to see.
Selecting any of these Blue dropdowns and selecting All, removes the
filter on that column, but would leave it still applied to any others.

The advantage of Show All, is that it will remove all filters that had
been applied in one go, taking you back to the full set of data.
Try again with adding it to your toolbar.
ViewToolbarsCustomiseCommandsCategoriesData
At this point, with Data highlit, you should see Commands in the right
hand pane of the Dialogue box.
Show All is the 5th item down.
Whilst holding your let mouse button down on Show All, drag it to the
toolbar, to a position between a couple of icons that are there, until
the cursor changes to a long black bar, then release the mouse button.
It doesn't seem to like it if you try and drop it in a blank area at the
end of the toolbar, and maybe this has been your problem.

Whilst in this Customise mode, also drag the Autofilter icon to your
toolbar, next to the Show All, then click Close.

Now when you want to apply Autofilter, just click the Autofilter button.
When you have selected a whole range of different filters and have a
smaller subset of your data showing, just click Show All to get back to
the full set, with all of the filter icons still in place.

For more help on Autofilter, take a look at Debra Dalgleish's site
http://www.contextures.com/xlautofilter01.html

--
Regards

Roger Govier


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

I tried holding down the control key, and selecting the columns I
wanted to
perform the operation on such that they were all highlighted
simultaneously;
I had to do it this way because they are non-adjascent columns.

When I did autofilter, I got an error message that said the command I
chose
could not be performed with multiple sections, and that I had to
select a
single range and try again.

I suppose I could copy all the relevant columns to another location,
arrange
them adjascently, and performe the filtering operation on the bunch of
them
that way. As long as all the pertinent rows matched, it would produce
the
same desired result. If you know of a quicker easier way to perform
this
operation on non-adjascent columns, please kindly share your
knowledge.

It's doesn't matter that much, though, because by clicking "All" on
the
dropdown menu, as you have explained, and playing with the autofilter
function on the other columns, I am able to get the knowledge I am
looking
for in a fairly expedient fashion. What's going on is I have three
columns
in which I am looking for entries greater than zero. There is one
primary
column in which I am looking for entries greater than zero, but I need
to
check and make sure that the other two columns don't have entries
greater
than zero in rows in which the primary column is blank. Performing
the
autofilter on the auxiliary rows, and then checking the primary column
for
blank spaces accomplishes that task fairly efficiently, now that I
know about
"All" on the dropdown menu, thanks to you.

Your instructions on how to create the "Show All" icon on my toolbar
didn't
seem to work for me, but does this just do the same thing as selecting
"All"
on the column dropdown menu as I have been doing?

Mick

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 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 filter to it. I see that its arrow turned
blue.

Then I clicked the blue arrow on the primary column, selected "All". This
just seems to have undone the filtering operation, but maintained the
highlighting on the entire range I highlighted originally. It doesn't seem
that the filtering was done on any of the other columns.

I think I should be ok with just applying the filter to one column at a
time, but with the area highlighted as you've suggested. With the All icon
button, I now can undo it quickly and go do the fltering on the auxilliary
columns using the Autofilter icon button, and check for any blank spaces in
the primary column by simple visual inspection, and do this pretty quickly
enough.

This sounds like what you are reccommending when you say, "Just use the
dropdowns on any columns that you wish to filter by". I guess I was just
looking for an even quicker more efficient way to filter all three colums at
once for all rows that had at least one entry greater than zero. If you know
how to do this, please let me know. Otherwise, I am fine for the time being
applying what I've learned from you thus far.

Thanks for your help.

Mick


"Roger Govier" wrote:

Hi Mick

I fear I may have mislead you with my instructions.
It does not matter whether there are any blank columns within your data,
or whether there are columns that you don't wish to filter.
I usually mark the complete range then DataFilterautofilter to be sure
that the filter is available on all columns that I wish to use, even if
some are blank within the range. You can click on any cell within your
data and choose DataFilterAutofilter, but if you have any blank
columns, then it will think the table only applies up to the first blank
column, and will not apply the dropdown to other columns beyond that.

Just use the dropdowns on any columns that you wish to filter by.
When a filter is applied, the small downward pointing arrow on the
filter button, turns Blue, but this can sometimes be difficult to see.
Selecting any of these Blue dropdowns and selecting All, removes the
filter on that column, but would leave it still applied to any others.

The advantage of Show All, is that it will remove all filters that had
been applied in one go, taking you back to the full set of data.
Try again with adding it to your toolbar.
ViewToolbarsCustomiseCommandsCategoriesData
At this point, with Data highlit, you should see Commands in the right
hand pane of the Dialogue box.
Show All is the 5th item down.
Whilst holding your let mouse button down on Show All, drag it to the
toolbar, to a position between a couple of icons that are there, until
the cursor changes to a long black bar, then release the mouse button.
It doesn't seem to like it if you try and drop it in a blank area at the
end of the toolbar, and maybe this has been your problem.

Whilst in this Customise mode, also drag the Autofilter icon to your
toolbar, next to the Show All, then click Close.

Now when you want to apply Autofilter, just click the Autofilter button.
When you have selected a whole range of different filters and have a
smaller subset of your data showing, just click Show All to get back to
the full set, with all of the filter icons still in place.

For more help on Autofilter, take a look at Debra Dalgleish's site
http://www.contextures.com/xlautofilter01.html

--
Regards

Roger Govier


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

I tried holding down the control key, and selecting the columns I
wanted to
perform the operation on such that they were all highlighted
simultaneously;
I had to do it this way because they are non-adjascent columns.

When I did autofilter, I got an error message that said the command I
chose
could not be performed with multiple sections, and that I had to
select a
single range and try again.

I suppose I could copy all the relevant columns to another location,
arrange
them adjascently, and performe the filtering operation on the bunch of
them
that way. As long as all the pertinent rows matched, it would produce
the
same desired result. If you know of a quicker easier way to perform
this
operation on non-adjascent columns, please kindly share your
knowledge.

It's doesn't matter that much, though, because by clicking "All" on
the
dropdown menu, as you have explained, and playing with the autofilter
function on the other columns, I am able to get the knowledge I am
looking
for in a fairly expedient fashion. What's going on is I have three
columns
in which I am looking for entries greater than zero. There is one
primary
column in which I am looking for entries greater than zero, but I need
to
check and make sure that the other two columns don't have entries
greater
than zero in rows in which the primary column is blank. Performing
the
autofilter on the auxiliary rows, and then checking the primary column
for
blank spaces accomplishes that task fairly efficiently, now that I
know about
"All" on the dropdown menu, thanks to you.

Your instructions on how to create the "Show All" icon on my toolbar
didn't
seem to work for me, but does this just do the same thing as selecting
"All"
on the column dropdown menu as I have been doing?

Mick

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 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 filter to it. I see that its arrow turned
blue.

Then I clicked the blue arrow on the primary column, selected "All". This
just seems to have undone the filtering operation, but maintained the
highlighting on the entire range I highlighted originally. It doesn't seem
that the filtering was done on any of the other columns.

I think I should be ok with just applying the filter to one column at a
time, but with the area highlighted as you've suggested. With the All icon
button, I now can undo it quickly and go do the fltering on the auxilliary
columns using the Autofilter icon button, and check for any blank spaces in
the primary column by simple visual inspection, and do this pretty quickly
enough.

This sounds like what you are reccommending when you say, "Just use the
dropdowns on any columns that you wish to filter by". I guess I was just
looking for an even quicker more efficient way to filter all three colums at
once for all rows that had at least one entry greater than zero. If you know
how to do this, please let me know. Otherwise, I am fine for the time being
applying what I've learned from you thus far.

Thanks for your help.

Mick


"Roger Govier" wrote:

Hi Mick

I fear I may have mislead you with my instructions.
It does not matter whether there are any blank columns within your data,
or whether there are columns that you don't wish to filter.
I usually mark the complete range then DataFilterautofilter to be sure
that the filter is available on all columns that I wish to use, even if
some are blank within the range. You can click on any cell within your
data and choose DataFilterAutofilter, but if you have any blank
columns, then it will think the table only applies up to the first blank
column, and will not apply the dropdown to other columns beyond that.

Just use the dropdowns on any columns that you wish to filter by.
When a filter is applied, the small downward pointing arrow on the
filter button, turns Blue, but this can sometimes be difficult to see.
Selecting any of these Blue dropdowns and selecting All, removes the
filter on that column, but would leave it still applied to any others.

The advantage of Show All, is that it will remove all filters that had
been applied in one go, taking you back to the full set of data.
Try again with adding it to your toolbar.
ViewToolbarsCustomiseCommandsCategoriesData
At this point, with Data highlit, you should see Commands in the right
hand pane of the Dialogue box.
Show All is the 5th item down.
Whilst holding your let mouse button down on Show All, drag it to the
toolbar, to a position between a couple of icons that are there, until
the cursor changes to a long black bar, then release the mouse button.
It doesn't seem to like it if you try and drop it in a blank area at the
end of the toolbar, and maybe this has been your problem.

Whilst in this Customise mode, also drag the Autofilter icon to your
toolbar, next to the Show All, then click Close.

Now when you want to apply Autofilter, just click the Autofilter button.
When you have selected a whole range of different filters and have a
smaller subset of your data showing, just click Show All to get back to
the full set, with all of the filter icons still in place.

For more help on Autofilter, take a look at Debra Dalgleish's site
http://www.contextures.com/xlautofilter01.html

--
Regards

Roger Govier


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

I tried holding down the control key, and selecting the columns I
wanted to
perform the operation on such that they were all highlighted
simultaneously;
I had to do it this way because they are non-adjascent columns.

When I did autofilter, I got an error message that said the command I
chose
could not be performed with multiple sections, and that I had to
select a
single range and try again.

I suppose I could copy all the relevant columns to another location,
arrange
them adjascently, and performe the filtering operation on the bunch of
them
that way. As long as all the pertinent rows matched, it would produce
the
same desired result. If you know of a quicker easier way to perform
this
operation on non-adjascent columns, please kindly share your
knowledge.

It's doesn't matter that much, though, because by clicking "All" on
the
dropdown menu, as you have explained, and playing with the autofilter
function on the other columns, I am able to get the knowledge I am
looking
for in a fairly expedient fashion. What's going on is I have three
columns
in which I am looking for entries greater than zero. There is one
primary
column in which I am looking for entries greater than zero, but I need
to
check and make sure that the other two columns don't have entries
greater
than zero in rows in which the primary column is blank. Performing
the
autofilter on the auxiliary rows, and then checking the primary column
for
blank spaces accomplishes that task fairly efficiently, now that I
know about
"All" on the dropdown menu, thanks to you.

Your instructions on how to create the "Show All" icon on my toolbar
didn't
seem to work for me, but does this just do the same thing as selecting
"All"
on the column dropdown menu as I have been doing?

Mick

Hi Mick

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 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 filter to it. I see that its arrow
turned
blue.

Then I clicked the blue arrow on the primary column, selected "All".
This
just seems to have undone the filtering operation, but maintained the
highlighting on the entire range I highlighted originally. It doesn't
seem
that the filtering was done on any of the other columns.

I think I should be ok with just applying the filter to one column at
a
time, but with the area highlighted as you've suggested. With the All
icon
button, I now can undo it quickly and go do the fltering on the
auxilliary
columns using the Autofilter icon button, and check for any blank
spaces in
the primary column by simple visual inspection, and do this pretty
quickly
enough.

This sounds like what you are reccommending when you say, "Just use
the
dropdowns on any columns that you wish to filter by". I guess I was
just
looking for an even quicker more efficient way to filter all three
colums at
once for all rows that had at least one entry greater than zero. If
you know
how to do this, please let me know. Otherwise, I am fine for the time
being
applying what I've learned from you thus far.

Thanks for your help.

Mick


"Roger Govier" wrote:

Hi Mick

I fear I may have mislead you with my instructions.
It does not matter whether there are any blank columns within your
data,
or whether there are columns that you don't wish to filter.
I usually mark the complete range then DataFilterautofilter to be
sure
that the filter is available on all columns that I wish to use, even
if
some are blank within the range. You can click on any cell within
your
data and choose DataFilterAutofilter, but if you have any blank
columns, then it will think the table only applies up to the first
blank
column, and will not apply the dropdown to other columns beyond that.

Just use the dropdowns on any columns that you wish to filter by.
When a filter is applied, the small downward pointing arrow on the
filter button, turns Blue, but this can sometimes be difficult to
see.
Selecting any of these Blue dropdowns and selecting All, removes the
filter on that column, but would leave it still applied to any
others.

The advantage of Show All, is that it will remove all filters that
had
been applied in one go, taking you back to the full set of data.
Try again with adding it to your toolbar.
ViewToolbarsCustomiseCommandsCategoriesData
At this point, with Data highlit, you should see Commands in the
right
hand pane of the Dialogue box.
Show All is the 5th item down.
Whilst holding your let mouse button down on Show All, drag it to the
toolbar, to a position between a couple of icons that are there,
until
the cursor changes to a long black bar, then release the mouse
button.
It doesn't seem to like it if you try and drop it in a blank area at
the
end of the toolbar, and maybe this has been your problem.

Whilst in this Customise mode, also drag the Autofilter icon to your
toolbar, next to the Show All, then click Close.

Now when you want to apply Autofilter, just click the Autofilter
button.
When you have selected a whole range of different filters and have a
smaller subset of your data showing, just click Show All to get back
to
the full set, with all of the filter icons still in place.

For more help on Autofilter, take a look at Debra Dalgleish's site
http://www.contextures.com/xlautofilter01.html

--
Regards

Roger Govier


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

I tried holding down the control key, and selecting the columns I
wanted to
perform the operation on such that they were all highlighted
simultaneously;
I had to do it this way because they are non-adjascent columns.

When I did autofilter, I got an error message that said the command
I
chose
could not be performed with multiple sections, and that I had to
select a
single range and try again.

I suppose I could copy all the relevant columns to another
location,
arrange
them adjascently, and performe the filtering operation on the bunch
of
them
that way. As long as all the pertinent rows matched, it would
produce
the
same desired result. If you know of a quicker easier way to
perform
this
operation on non-adjascent columns, please kindly share your
knowledge.

It's doesn't matter that much, though, because by clicking "All" on
the
dropdown menu, as you have explained, and playing with the
autofilter
function on the other columns, I am able to get the knowledge I am
looking
for in a fairly expedient fashion. What's going on is I have three
columns
in which I am looking for entries greater than zero. There is one
primary
column in which I am looking for entries greater than zero, but I
need
to
check and make sure that the other two columns don't have entries
greater
than zero in rows in which the primary column is blank. Performing
the
autofilter on the auxiliary rows, and then checking the primary
column
for
blank spaces accomplishes that task fairly efficiently, now that I
know about
"All" on the dropdown menu, thanks to you.

Your instructions on how to create the "Show All" icon on my
toolbar
didn't
seem to work for me, but does this just do the same thing as
selecting
"All"
on the column dropdown menu as I have been doing?

Mick

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, by simple
visual inspection, I can determine if there are any blank cells in the
primary column, and copy those rows into a new worksheet.

I was looking for a quick and efficient way to filter the worksheet for all
rows that had at least one entry greater than zero in at least one of the
three pertinent columns that could be done all at once, in one simple stroke,
and without the visual inspection described above. If you know of such a
way, please describe that to me.

If Excel cannot do that, that's ok, you've been very helpful. Thanks.

Mick



"Roger Govier" wrote:

Hi Mick

I fear I may have mislead you with my instructions.
It does not matter whether there are any blank columns within your data,
or whether there are columns that you don't wish to filter.
I usually mark the complete range then DataFilterautofilter to be sure
that the filter is available on all columns that I wish to use, even if
some are blank within the range. You can click on any cell within your
data and choose DataFilterAutofilter, but if you have any blank
columns, then it will think the table only applies up to the first blank
column, and will not apply the dropdown to other columns beyond that.

Just use the dropdowns on any columns that you wish to filter by.
When a filter is applied, the small downward pointing arrow on the
filter button, turns Blue, but this can sometimes be difficult to see.
Selecting any of these Blue dropdowns and selecting All, removes the
filter on that column, but would leave it still applied to any others.

The advantage of Show All, is that it will remove all filters that had
been applied in one go, taking you back to the full set of data.
Try again with adding it to your toolbar.
ViewToolbarsCustomiseCommandsCategoriesData
At this point, with Data highlit, you should see Commands in the right
hand pane of the Dialogue box.
Show All is the 5th item down.
Whilst holding your let mouse button down on Show All, drag it to the
toolbar, to a position between a couple of icons that are there, until
the cursor changes to a long black bar, then release the mouse button.
It doesn't seem to like it if you try and drop it in a blank area at the
end of the toolbar, and maybe this has been your problem.

Whilst in this Customise mode, also drag the Autofilter icon to your
toolbar, next to the Show All, then click Close.

Now when you want to apply Autofilter, just click the Autofilter button.
When you have selected a whole range of different filters and have a
smaller subset of your data showing, just click Show All to get back to
the full set, with all of the filter icons still in place.

For more help on Autofilter, take a look at Debra Dalgleish's site
http://www.contextures.com/xlautofilter01.html

--
Regards

Roger Govier


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

I tried holding down the control key, and selecting the columns I
wanted to
perform the operation on such that they were all highlighted
simultaneously;
I had to do it this way because they are non-adjascent columns.

When I did autofilter, I got an error message that said the command I
chose
could not be performed with multiple sections, and that I had to
select a
single range and try again.

I suppose I could copy all the relevant columns to another location,
arrange
them adjascently, and performe the filtering operation on the bunch of
them
that way. As long as all the pertinent rows matched, it would produce
the
same desired result. If you know of a quicker easier way to perform
this
operation on non-adjascent columns, please kindly share your
knowledge.

It's doesn't matter that much, though, because by clicking "All" on
the
dropdown menu, as you have explained, and playing with the autofilter
function on the other columns, I am able to get the knowledge I am
looking
for in a fairly expedient fashion. What's going on is I have three
columns
in which I am looking for entries greater than zero. There is one
primary
column in which I am looking for entries greater than zero, but I need
to
check and make sure that the other two columns don't have entries
greater
than zero in rows in which the primary column is blank. Performing
the
autofilter on the auxiliary rows, and then checking the primary column
for
blank spaces accomplishes that task fairly efficiently, now that I
know about
"All" on the dropdown menu, thanks to you.

Your instructions on how to create the "Show All" icon on my toolbar
didn't
seem to work for me, but does this just do the same thing as selecting
"All"
on the column dropdown menu as I have been doing?

Mick

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, by
simple
visual inspection, I can determine if there are any blank cells in the
primary column, and copy those rows into a new worksheet.

I was looking for a quick and efficient way to filter the worksheet
for all
rows that had at least one entry greater than zero in at least one of
the
three pertinent columns that could be done all at once, in one simple
stroke,
and without the visual inspection described above. If you know of
such a
way, please describe that to me.

If Excel cannot do that, that's ok, you've been very helpful. Thanks.

Mick

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, by
simple
visual inspection, I can determine if there are any blank cells in the
primary column, and copy those rows into a new worksheet.

I was looking for a quick and efficient way to filter the worksheet
for all
rows that had at least one entry greater than zero in at least one of
the
three pertinent columns that could be done all at once, in one simple
stroke,
and without the visual inspection described above. If you know of
such a
way, please describe that to me.

If Excel cannot do that, that's ok, you've been very helpful. Thanks.

Mick

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,
by
simple
visual inspection, I can determine if there are any blank cells in
the
primary column, and copy those rows into a new worksheet.

I was looking for a quick and efficient way to filter the worksheet
for all
rows that had at least one entry greater than zero in at least one
of
the
three pertinent columns that could be done all at once, in one
simple
stroke,
and without the visual inspection described above. If you know of
such a
way, please describe that to me.

If Excel cannot do that, that's ok, you've been very helpful.
Thanks.

Mick

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,
by
simple
visual inspection, I can determine if there are any blank cells in
the
primary column, and copy those rows into a new worksheet.

I was looking for a quick and efficient way to filter the worksheet
for all
rows that had at least one entry greater than zero in at least one
of
the
three pertinent columns that could be done all at once, in one
simple
stroke,
and without the visual inspection described above. If you know of
such a
way, please describe that to me.

If Excel cannot do that, that's ok, you've been very helpful.
Thanks.

Mick

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,
by
simple
visual inspection, I can determine if there are any blank cells
in
the
primary column, and copy those rows into a new worksheet.

I was looking for a quick and efficient way to filter the
worksheet
for all
rows that had at least one entry greater than zero in at least
one
of
the
three pertinent columns that could be done all at once, in one
simple
stroke,
and without the visual inspection described above. If you know
of
such a
way, please describe that to me.

If Excel cannot do that, that's ok, you've been very helpful.
Thanks.

Mick

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,
by
simple
visual inspection, I can determine if there are any blank cells
in
the
primary column, and copy those rows into a new worksheet.

I was looking for a quick and efficient way to filter the
worksheet
for all
rows that had at least one entry greater than zero in at least
one
of
the
three pertinent columns that could be done all at once, in one
simple
stroke,
and without the visual inspection described above. If you know
of
such a
way, please describe that to me.

If Excel cannot do that, that's ok, you've been very helpful.
Thanks.

Mick

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,
by
simple
visual inspection, I can determine if there are any blank
cells
in
the
primary column, and copy those rows into a new worksheet.

I was looking for a quick and efficient way to filter the
worksheet
for all
rows that had at least one entry greater than zero in at
least
one
of
the
three pertinent columns that could be done all at once, in
one
simple
stroke,
and without the visual inspection described above. If you
know
of
such a
way, please describe that to me.

If Excel cannot do that, that's ok, you've been very helpful.
Thanks.

Mick

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,