copy cells in a row to another sheet

A

AM

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.
 
M

Max

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top