Selecting rows with the same criteria

  • Thread starter Thread starter Ruslan
  • Start date Start date
R

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
 
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))))
 
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
 
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
 
Back
Top