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

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

One way ..
Assume your source data is in Sheet1 cols A to D, data from row1 down where
the key col = col A
In Sheet2,
In A1: =IF(Sheet1!A1=1,ROW(),"")
In B1: =IF(ROW()COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($ A:$A,ROW())))
Copy B1 to E1. Select A1:E1, copy down to cover the max expected extent of
source data say down to E100? Minimize col A. Cols B to E returns the
required lines, all neatly packed at the top. Joy? hit the YES below
--
Max
Singapore
---
"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.

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

Works for me..with data in Sheet1 Col A to D.

If it is returning an error make sure you enter the formula using
Ctrl+Shift+ Enter instead of just Enter

--
Jacob


"Ruslan" wrote:

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

Hi,

You may refer to my article here

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Ruslan" wrote in message
...
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