I have a worksheet with twelve columns and 200 rows of data.
If column A is NOTBLANK and Column J is NOT= 1, then I want to copy cells in
column A, B, C and D to another worksheet. All of these cells from the same
row. If either condition doesn't match, then skip to the next row. The result
would be a compact report of about 40 rows with entry in column A ,B, C and D.

See previous response
--
Gary''s Student - gsnu200739


"AM" wrote:

I have a worksheet with twelve columns and 200 rows of data.
If column A is NOTBLANK and Column J is NOT= 1, then I want to copy cells in
column A, B, C and D to another worksheet. All of these cells from the same
row. If either condition doesn't match, then skip to the next row. The result
would be a compact report of about 40 rows with entry in column A ,B, C and D.

If column A is NOTBLANK and Column J is NOT= 1

You can adapt the sample I provided in response to your earlier 2 postings:
http://www.savefile.com/files/1000526
Show lines satisfying criteria in another sht.xls

Just make a copy of the sheet: Output

In the copy,

Change the criteria in A2 to read as:
=IF(AND(X!A2<"",X!J2<1),ROW(),"")
Copy A2 down. That's it. No change required to the formulas in cols B to D.
Cols B to D will return the required compacted results from cols B to D in X.

------ ------
And if you really wanted col A from X to be returned as well (I read this as
a typo in your posting here)

Just change B2 to read as:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(X!A:A,SMALL($ A:$A,ROWS($1:1))))
Copy B2 to E2, fill down to E200, done! You've just extended the returns to
cover col A in X as well.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AM" wrote:
I have a worksheet with twelve columns and 200 rows of data.
If column A is NOTBLANK and Column J is NOT= 1, then I want to copy cells in
column A, B, C and D to another worksheet. All of these cells from the same
row. If either condition doesn't match, then skip to the next row. The result
would be a compact report of about 40 rows with entry in column A ,B, C and D.