Removing gaps from data extraction

Hello
I'm doing some simple data extraction, e.g.
A B
1 bob 3
2 mandy 4
3 charlie 6
4 dave 1
5 steve 5

So I had in c1 to c5 = =if(b1 > 3, a1,"") autofilled, which works fine, but I end up with,

gap
mandy
charlie
gap
steve

how would I get,

mandy
charlie
steve

also is it possible to have an if statement in 1 cell change the value of another cell?

e.g.
in a1
if(b1>5, c1="yes",c1="no"), can't seem to get it to work

Thanks for your time.

One way in xl2007

select E1:E5 and then enter this array formula in E1, comit with CTRL+SHIFT+ENTER

=IFERROR(INDEX(A1:A5,SMALL(IF(B1:B5>3,ROW(B1:B5),ROW(A6)),ROW(A1:A5))),"")

just need to check for the error in pre '07

=IF(ISERROR(INDEX(A1:A5,SMALL(IF(B1:B5>3,ROW(B1:B5),ROW(A6)),ROW(A1:A5)))),"",INDEX(A1:A5,SMALL(IF(B1:B5>3,ROW(B1:B5),ROW(A6)),ROW(A1:A5))))


You might want to change you profile if you are not using 2007.

I may have perhaps over simplified this, I have a legal database with name, claim, claimed amount, settlement amount, etc, my boss basically wants to be able to put in a date or a date range and have it, pull out the entire rows where the date settled column has a matching date.

I've tried some very basic things along the lines of

if(date settled = date entered, then paste the entire row into a new workbook, else skip that row) which actually worked when it was just one row couldn't figure out how to make it do it for a range.

I'm wondering if perhaps some kind of lookup or indexing may be easier or more efficient.

this works

Please Login or Register  to view this content.

these don't

Please Login or Register  to view this content.

Use AutoFilter then copy visible range to new worksheet.

Thanks that's sorted the gaps.

About the selecting and copying of the records however, as I said this works

Please Login or Register  to view this content.

would it be possible to create like a case, where it checks P1 and then if it's valid, it loops until it's copied all of row 1, then moves onto Row 2, and checks P2 etc etc and so on, skipping to the next row straight away if P is invalid of course.

Basically a shorter version of,

Please Login or Register  to view this content.

You do not have to loop, just copy it all at once.
See attached example.

Please Login or Register  to view this content.

Yep, you have managed to confuse me

Please Login or Register  to view this content.

Ah that's it, perfect, I just need that to loop, doing p1 then p2 then p3 and copying into a1 then a2 then a3 etc

Skipping the rows where P is = to 0

edit
I figured it out o.o

Please Login or Register  to view this content.

The code will create gaps in the outputted records on sheet4.
If you only increment the j variable after a transfer then the gaps should disappear.

Please Login or Register  to view this content.