Prev: Sumproduct for Values over a certain Value eg 0.00
Next: find smallest positive number among datas
From: Ruslan on 16 Mar 2010 04:04
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
From: Jacob Skaria on 16 Mar 2010 04:57
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))))

--
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
From: Max on 16 Mar 2010 04:58
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.

From: Ruslan on 16 Mar 2010 06:23
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$1000=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
From: Jacob Skaria on 16 Mar 2010 06:48
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$1000=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
 |  Next  |  Last
Pages: 1 2
Prev: Sumproduct for Values over a certain Value eg 0.00
Next: find smallest positive number among datas