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 not(blank) and column J has a 1 [number one], then I want
items in columns A,B, C and D to be copied to another sheet. Obviously if
either condition doesn't match it would skip over and got ot the next row.
When its all done, it would give me a compact list of data from only 40 to 50
rows.
Thanks for any help,
Atul
 
G

Gary''s Student

Use AutoFiler
pick non-blanks for col A and 1 for col J
Copy the visible cells and paste them elsewhere.
 
M

Max

As responsed in your earlier posting, here's another play to try which should
deliver both the automation and compactness (all neatly bunched at the top)
aspects ..

Illustrated in this sample:
http://www.savefile.com/files/1000526
Show lines satisfying criteria in another sht.xls

Assume source table as per posting is in sheet: X, cols A to J, data from
row2 down, where the key criteria cols are col A and J

In another sheet: Output (say)

In A2:
=IF(AND(X!A2<>"",X!J2=1),ROW(),"")
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(X!B:B,SMALL($A:$A,ROWS($1:1))))
Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of
data in X, eg down to D200? Hide away col A. Cols B to D will return the
required results, all neatly bunched at the top.
 

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