Selecting rows with the same criteria

[Ruslan]

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

 

[Jacob Skaria]

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$1000=1,
ROW($A$1:$A$1000)),ROW(A1))))

 

[Max]

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

 

[Ruslan]

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

 

[Jacob Skaria]

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

 

[Ashish Mathur]

Hi,

You may refer to my article here

http://office.microsoft.com/en-us/excel/HA012260381033.aspx