Copy and paste data from Auto Filter / Subtotal (or apply a formula to filtered data)

[Harry Flashman]

I have Excel 2003 at work and Excel 2000 on one of my computers at
home. I am not sure how much this feature differs between the two
programs, but I am defintitely experiencing this problem on my work
computer:

When I apply autofilter and then copy the data and try to paste it
onto another worksheet, it pastes all the data, not just the visible
data (i.e the data that is still visible when autofilter is applied).

I have just tried this on my home computer (2000) and the results are
a little more unpredictable.
Sometimes it will paste only the visible data, but sometimes it pastes
all the data.
What is going on here? How can I ensure that it only pastes the
visible data, everytime? Or rather how can I ensure that it paste what
I wish, whether that be visible or not visible data?

A similar problem occurs when I use Subtotal. Some times I only want
to copy and paste only the subtotals, but when I try to paste the data
to another sheet it pastes all the data, not just the summary.

Lastly one more question. Can I appy a formula such as MIN or MAX or
AVERAGE to data that has been autofiltered? Thus comparing only the
visible data?

For example: Lets say I have a spreadsheet consisting of products
belonging to different brands:
Column A: Brands
Column B: Products
Column C: Values

I filter the data by brand. Now I want to know what the highest value
of the filtered data is. If I use MAX and apply it to the range, it
seems to include the non-visible data. Is there way to ensure that it
only acts on the visible data? (I don't think there is such as formula
as MAXIF - or is there one?)

Harry

 

[Pete_UK]

Harry,

I don't have an answer to the first part of your question, as when I
copy filtered data then only the visible data gets pasted (XL2k).

However, for the second part you should look in Excel Help for the
SUBTOTAL function. This is used like this:

=SUBTOTAL(number,range)

where number defines what type of operation to carry out (eg 9 means
SUM). This only acts on the visible rows, and there are about 12
different values of number to give COUNT, COUNTA, MAX etc. I usually
put these formulae on the top row with filters and headers starting in
row3, so they are visible all the time without having to scroll down
to the bottom of the data.

Hope this helps.

Pete

 

[Harry Flashman]

This seems silly answering my own question but just so I don't waste
anyone's time. I have worked out the answer to at least one of my
questions.
Yes there is a (kind of) MAXIF type funtion:
Column Column
A B
Tom 1
Dick 2
Harry 6
Tom 12
Dick 1
Harry 2

Name the range in column A - Name (or just use the cell reference
A2:A7)
=MAX((Name="Tom")*(B2:B7)) then press CTRL+SHIFT+ENTER to put the
curly brackets around.
Voila the maximum = 12

I found this here:
http://www.dailydoseofexcel.com/archives/2004/06/29/maxif-minif-functions/

One thing I don't understand though: I tried to modify the formula
slightly:
=MAX((Name=$A$2)*(B2:B7)) CTRL+SHIFT+ENTER
All I got was #VALUE!

Btw, I still don't know the answer to my other questions.

 

[Harry Flashman]

Harry,

I don't have an answer to the first part of your question, as when I
copy filtered data then only the visible data gets pasted (XL2k).

However, for the second part you should look in Excel Help for the
SUBTOTAL function. This is used like this:

=SUBTOTAL(number,range)

where number defines what type of operation to carry out (eg 9 means
SUM). This only acts on the visible rows, and there are about 12
different values of number to give COUNT, COUNTA, MAX etc. I usually
put these formulae on the top row with filters and headers starting in
row3, so they are visible all the time without having to scroll down
to the bottom of the data.

Hope this helps.

Pete

I'll check that formula out thanks very much. But actually I was
refering to the feature on the menu at the top under Data - I just had
a l closer look and I can see now that it is called "Subtotals" .

 

[Harry Flashman]

Harry,

I don't have an answer to the first part of your question, as when I
copy filtered data then only the visible data gets pasted (XL2k).

However, for the second part you should look in Excel Help for the
SUBTOTAL function. This is used like this:

=SUBTOTAL(number,range)

where number defines what type of operation to carry out (eg 9 means
SUM). This only acts on the visible rows, and there are about 12
different values of number to give COUNT, COUNTA, MAX etc. I usually
put these formulae on the top row with filters and headers starting in
row3, so they are visible all the time without having to scroll down
to the bottom of the data.

Hope this helps.

Pete

Just looked up the formula Subtotal. Very clever and very useful.

=subtotal(function_num,ref1,ref2..)
That is great the way functions 1 to 11 apply to all data. But 101 to
111 only to visible data.
Just what I have been looking for, thanks.

 

[Pete_UK]

The normal numbers 1-11 exclude cells in rows that have been hidden by
means of filters, but the numbers 101-111 exclude cells in rows which
have been hidden by formatting as well as filters - not available in
XL 2000.

Pete

 

[Harry Flashman]

The normal numbers 1-11 exclude cells in rows that have been hidden by
means of filters, but the numbers 101-111 exclude cells in rows which
have been hidden by formatting as well as filters - not available in
XL 2000.

Pete

I only tried three digit functions and they worked well. I must have
misread the part about 1 to 11. Thanks.

 

[Pete_UK]

You're welcome, Harry.

Pete

I only tried three digit functions and they worked well. I must have
misread the part about 1 to 11. Thanks.