Copying cells based on a criterion

C

Chris_t_2k5

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
 
M

Max

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
 
Top