extract data from one worksheet to another worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff - 02 May 2008 21:13 GMT

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?

Reply to this Message

Peo Sjoblom - 02 May 2008 21:25 GMT

Advanced filter can do it

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

Signature

Regards,

Peo Sjoblom

>I have a list of data on one worksheet:
>
[quoted text clipped - 7 lines]
> function
> that will do this for me?

Reply to this Message

Reitanos - 02 May 2008 21:29 GMT

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.

> I have a list of data on one worksheet:
>
[quoted text clipped - 5 lines]
> new worksheet without sorting and cutting and pasting. Is there a function
> that will do this for me?

Reply to this Message

Reitanos - 02 May 2008 21:32 GMT

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.

> Do you want to count them, copy the data, or move the data?
>
[quoted text clipped - 11 lines]
> > new worksheet without sorting and cutting and pasting. Is there a function
> > that will do this for me?

Reply to this Message

Max - 02 May 2008 21:33 GMT

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,SMALL($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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>I have a list of data on one worksheet:
>
[quoted text clipped - 7 lines]
> function
> that will do this for me?

Reply to this Message

Teethless mama - 02 May 2008 23:21 GMT

Sheet 2

B2:
=IF(ISERR(SMALL(IF(Fruit=$A$2,ROW(INDIRECT("1:"&ROWS(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

> I have a list of data on one worksheet:
>
[quoted text clipped - 5 lines]
> new worksheet without sorting and cutting and pasting. Is there a function
> that will do this for me?

Reply to this Message