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

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" <Christ2k5@discussions.microsoft.com> wrote in message news:27B3C522-7DC2-45FB-BD32-AF69469521EC@microsoft.com...
> 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

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" <Christ2k5@discussions.microsoft.com> wrote in message
news:27B3C522-7DC2-45FB-BD32-AF69469521EC@microsoft.com...
> 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

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

You're welcome, Chris
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Chris_t_2k5" <Christ2k5@discussions.microsoft.com> wrote in message
news:82ABF8E0-BB51-4BBB-813F-C1A04D394DAD@microsoft.com...
> Thanks i'll have a go at these and let you know how i get on