Jacob,
seems like smth wrong 'cause formula doesn't work.
Rgds,
Ruslan


"Jacob Skaria" wrote:

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

With data in Sheet1 apply the below formula in Sheet2 cell A1 and copy
down/across as required..This will retrieve the rows having 1 in colA

=IF(COUNTIF(Sheet1!$A$1:$A$1000,1)<ROW(A1),"",
INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$A$1:$A$10 00=1,
ROW($A$1:$A$1000)),ROW(A1))))

--
Jacob


"Ruslan" wrote:

Dear All,
I got a list of data like:
A B C D
1 1 ABC 55 Wilson
2 AFD 60 Adams
3 1 DGC 76 Korinth
4 AFV 83 Smith
5 1 URD 45 Hertz
...
and I would like the Excel automatically compose table on a new sheet that
will show all rows with "1" in A column. Without Pivot Table. In other words,
what formula should the cell in new sheet have to choose the first row with
"1" then the next "1" and etc.
Rgds,
Ruslan