I have a list of data on one worksheet:

Apples True
Banana False
Apples False

I want to pull just the Apples and the corresponding data (true, false) to a
new worksheet without sorting and cutting and pasting. Is there a function
that will do this for me?

Advanced filter can do it

http://www.contextures.com/xladvfilter01.html



--


Regards,


Peo Sjoblom



"Jeff" wrote in message
...
I have a list of data on one worksheet:

Apples True
Banana False
Apples False

I want to pull just the Apples and the corresponding data (true, false) to
a
new worksheet without sorting and cutting and pasting. Is there a
function
that will do this for me?

Do you want to count them, copy the data, or move the data?

Counting can be done with simple formulas, but moving or copying could
be done with either a macro or the Data/Filter/Advanced Filter
command.

On May 2, 4:13 pm, Jeff wrote:
I have a list of data on one worksheet:

Apples True
Banana False
Apples False

I want to pull just the Apples and the corresponding data (true, false) to a
new worksheet without sorting and cutting and pasting. Is there a function
that will do this for me?

I meant to also say that if you don't require that another worksheet
is used, the Data/Filter/Autofilter command is incredibly flexible and
would save you a great deal of time.

On May 2, 4:29 pm, Reitanos wrote:
Do you want to count them, copy the data, or move the data?

Counting can be done with simple formulas, but moving or copying could
be done with either a macro or the Data/Filter/Advanced Filter
command.

On May 2, 4:13 pm, Jeff wrote:

I have a list of data on one worksheet:

Apples True
Banana False
Apples False

I want to pull just the Apples and the corresponding data (true, false) to a
new worksheet without sorting and cutting and pasting. Is there a function
that will do this for me?

Source data assumed in Sheet1, cols A & B, data from row2 down

In Sheet2,
In A2: =IF(Sheet1!A2="Apples",ROW(),"")
Leave A1 blank

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Sheet1!A:A,SM ALL($A:$A,ROWS($1:1))))
Copy B2 to C2. Select A2:C2, copy down to cover the max expected extent of
data in Sheet1. Minimize/hide away col A. Cols B & C returns the required
results, all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff" wrote in message
...
I have a list of data on one worksheet:

Apples True
Banana False
Apples False

I want to pull just the Apples and the corresponding data (true, false) to
a
new worksheet without sorting and cutting and pasting. Is there a
function
that will do this for me?

Sheet 2

B2:
=IF(ISERR(SMALL(IF(Fruit=$A$2,ROW(INDIRECT("1:"&RO WS(Fruit)))),ROWS($1:1))),"",INDEX(Logic,SMALL(IF( Fruit=$A$2,ROW(INDIRECT("1:"&ROWS(Fruit)))),ROWS($ 1:1))))

ctrl+shift+enter, not just enter
copy down


"Jeff" wrote:

I have a list of data on one worksheet:

Apples True
Banana False
Apples False

I want to pull just the Apples and the corresponding data (true, false) to a
new worksheet without sorting and cutting and pasting. Is there a function
that will do this for me?