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