One way to achieve this using non-array formulas.

Source data assumed in sheet: A, cols A to C, data from row2 down.
Col C = key col with the values either: offer or accept

In sheet: B,

Create a DV in A1 to select the options: offer or accept
(Data Validation, Allow: List, Source: accept, reject)

Paste the same col labels into B1:D1

In A2:
=IF($A$1="","",IF(A!C2="","",IF(A!C2=$A$1,ROW(),"" )))

In B2:
=IF(ROW(A1)COUNT($A:$A),"",INDEX(A!A:A,MATCH(SMAL L($A:$A,ROW(A1)),$A:$A,0)))
Copy B2 to D2

Then just select A2:D2 and fill down to cover the max expected extent of
data in A. The required results will appear in cols B to D, all neatly
bunched at the top, ie only lines corresponding to the selection made in the
DV in A1. If "accept" is selected, then only "accept" lines will appear.
Ditto for "reject".
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
Hi this is exactly what i am looking for..
I have A-sheet which has columns as below -
Dept group offer
abc aa reject
xyz bb accept

So I need all the the data from dept and group on B-sheet if the offer
is accept. If I put a plain 'if' formula then the data is getting
copied to the same row as in A-sheet leaving blank rows in between..



Pete_UK wrote:
Could do with a bit more detail, but your IF statement should be of
this form:

=IF(your_condition, action_if_true, action_if_false)

the "action_if_false" may be set to "" (i.e. return blanks), so you
will need to amend this part if you do not want blanks when the
condition is not met.

Hope this helps.

Pete

wrote:
Hi I need this information urgently...
Sheet 1 has some data. I put a "IF" condition which when satisfied
needs to input data into Sheet 2. But the problem is that if the
condition is not satisfied it is copying with blanks..