Copying cells based on a criterion
 

Here is my problem.

I have cells A1:a100 full of numbers.

I wish to select all numbers that are lower than 20 for example and paste
them into cel B1 and down.

I know i could copy and paste but I need more complicated calculations than
that.

THEREFORE is it possible to copy cells based on af statements etc??

Thanks

Copying cells based on a criterion
 

Hi Chris

Try this

If you filter with EasyFilter "Less than 20" and choose "Copy Next to your data"
you get what you want.

http://www.rondebruin.nl/easyfilter.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chris_t_2k5" wrote in message ...
Here is my problem.

I have cells A1:a100 full of numbers.

I wish to select all numbers that are lower than 20 for example and paste
them into cel B1 and down.

I know i could copy and paste but I need more complicated calculations than
that.

THEREFORE is it possible to copy cells based on af statements etc??

Thanks

Copying cells based on a criterion
 

Just another option to play with, using non-array formulas

Put in B1:
=IF(ISERROR(SMALL(C:C,ROW(A1))),"",
INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Put in C1: =IF(A1="","",IF(A1<20,ROW(),""))

Select B1:C1, copy down to C100

Col B auto-returns the required results, all neatly bunched at the top

Col C is the criteria col. Perhaps better than hardcoding the "20" in the
criteria,
for flexibility, we could simply point to a cell, say D1 for the criteria
value,
i.e. use instead in C1:
=IF(OR(A1="",$D$1=""),"",IF(A1<$D$1,ROW(),""))

In that way, we can easily change the criteria for the returns in col B
w/o having to amend and re-fill the formulas in col C
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Chris_t_2k5" wrote in message
...
Here is my problem.

I have cells A1:a100 full of numbers.

I wish to select all numbers that are lower than 20 for example and paste
them into cel B1 and down.

I know i could copy and paste but I need more complicated calculations
than
that.

THEREFORE is it possible to copy cells based on af statements etc??

Thanks

Copying cells based on a criterion
 

Thanks i'll have a go at these and let you know how i get on

Copying cells based on a criterion
 

You're welcome, Chris
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Chris_t_2k5" wrote in message
...
Thanks i'll have a go at these and let you know how i get on