String Formula
 

I want to check a cell for content and if there is none go to the next cell
in the colum and check.......etc. I have over 50 cells to check.
ie.... in cell P2 I want to check I2 for content. if I2 is blank then check
I3 and so on. If P2 has content then place it in I2.
I want to continue that same query in P3 on down, without duplicating an
answer.
If P2 & P10 have content then that content needs to appear in I2 & I3

String Formula
 

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}"

=INDEX(P:P,SMALL(IF($P$2:$P$100<"",ROW($P$2:$P$10 0)),ROW(A1)))


If this post helps click Yes
---------------
Jacob Skaria


"James" wrote:

I want to check a cell for content and if there is none go to the next cell
in the colum and check.......etc. I have over 50 cells to check.
ie.... in cell P2 I want to check I2 for content. if I2 is blank then check
I3 and so on. If P2 has content then place it in I2.
I want to continue that same query in P3 on down, without duplicating an
answer.
If P2 & P10 have content then that content needs to appear in I2 & I3

String Formula
 

A B
1 apples
2 apples bananas
3 pears
4
5 bananas
6
7 pears

Can you write an array formula to make the above work. I sorta understand
but not quite. THANX

"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}"

=INDEX(P:P,SMALL(IF($P$2:$P$100<"",ROW($P$2:$P$10 0)),ROW(A1)))


If this post helps click Yes
---------------
Jacob Skaria


"James" wrote:

I want to check a cell for content and if there is none go to the next cell
in the colum and check.......etc. I have over 50 cells to check.
ie.... in cell P2 I want to check I2 for content. if I2 is blank then check
I3 and so on. If P2 has content then place it in I2.
I want to continue that same query in P3 on down, without duplicating an
answer.
If P2 & P10 have content then that content needs to appear in I2 & I3

String Formula
 

=INDEX(A:A,SMALL(IF($A$2:$A$100<"",ROW($A$2:$A$10 0)),ROW(A1)))

Enter the formula using Ctrl+Shift+Enter; to return the 1st value in ColA.
Copy/drag the formula down to get the subsequent values

If this post helps click Yes
---------------
Jacob Skaria


"James" wrote:

A B
1 apples
2 apples bananas
3 pears
4
5 bananas
6
7 pears

Can you write an array formula to make the above work. I sorta understand
but not quite. THANX

"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}"

=INDEX(P:P,SMALL(IF($P$2:$P$100<"",ROW($P$2:$P$10 0)),ROW(A1)))


If this post helps click Yes
---------------
Jacob Skaria


"James" wrote:

I want to check a cell for content and if there is none go to the next cell
in the colum and check.......etc. I have over 50 cells to check.
ie.... in cell P2 I want to check I2 for content. if I2 is blank then check
I3 and so on. If P2 has content then place it in I2.
I want to continue that same query in P3 on down, without duplicating an
answer.
If P2 & P10 have content then that content needs to appear in I2 & I3

String Formula
 

Jacob's formula will do that. It could be more robust, though.

Assuming the data range is A1:A7.

Enter this array formula** in B1 and copy down until you get blanks:

=IF(ROWS(B$1:B1)COUNTA(A$1:A$7),"",INDEX(A:A,SMAL L(IF(A$1:A$7<"",ROW(A$1:A$7)),ROWS(B$1:B1))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"James" wrote in message
...
A B
1 apples
2 apples bananas
3 pears
4
5 bananas
6
7 pears

Can you write an array formula to make the above work. I sorta understand
but not quite. THANX

"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}"

=INDEX(P:P,SMALL(IF($P$2:$P$100<"",ROW($P$2:$P$10 0)),ROW(A1)))


If this post helps click Yes
---------------
Jacob Skaria


"James" wrote:

I want to check a cell for content and if there is none go to the next
cell
in the colum and check.......etc. I have over 50 cells to check.
ie.... in cell P2 I want to check I2 for content. if I2 is blank then
check
I3 and so on. If P2 has content then place it in I2.
I want to continue that same query in P3 on down, without duplicating
an
answer.
If P2 & P10 have content then that content needs to appear in I2 & I3

String Formula
 

Cool. That worked better than Jacob's. His did not input the first item in
the list.
Although yours gave me #NUM! after the last item. no biggie I guess.

Thanx

"T. Valko" wrote:

Jacob's formula will do that. It could be more robust, though.

Assuming the data range is A1:A7.

Enter this array formula** in B1 and copy down until you get blanks:

=IF(ROWS(B$1:B1)COUNTA(A$1:A$7),"",INDEX(A:A,SMAL L(IF(A$1:A$7<"",ROW(A$1:A$7)),ROWS(B$1:B1))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP

String Formula
 

Although yours gave me #NUM! after the last item.
=IF(ROWS(B$1:B1)COUNTA(A$1:A$7),"",INDEX(A:A,SMAL L(IF(A$1:A$7<"",ROW(A$1:A$7)),ROWS(B$1:B1))))

Hmmm...

The only way that's possible is if you have formulas in column A and some of
them return formula blanks "". To account for that change this portion:

COUNTA(A$1:A$7)

To:

COUNTIF(A$1:A$7,"?*")

--
Biff
Microsoft Excel MVP


"James" wrote in message
...
Cool. That worked better than Jacob's. His did not input the first item in
the list.
Although yours gave me #NUM! after the last item. no biggie I guess.

Thanx

"T. Valko" wrote:

Jacob's formula will do that. It could be more robust, though.

Assuming the data range is A1:A7.

Enter this array formula** in B1 and copy down until you get blanks:

=IF(ROWS(B$1:B1)COUNTA(A$1:A$7),"",INDEX(A:A,SMAL L(IF(A$1:A$7<"",ROW(A$1:A$7)),ROWS(B$1:B1))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP

String Formula
 

Thanks Biff.. (I missed the error handling part).

To handle numerics and text we can have the below....
=IF(ROW(A1)<=COUNTIF(A:A,"?*")+COUNT(A:A),INDEX(A: A,SMALL(IF($A$1:$A$100<"",ROW($A$1:$A$100)),ROW(A 1))),"")

One query: Is there any specific reason for using =ROWS(B$1:B1) instead of
ROW($B1)

James:

That worked better than Jacob's. His did not input the first item in the list.

In the intial post you have mentioned as P2 and hence I have referenced only
from cell 2 in the formula...

If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

Although yours gave me #NUM! after the last item.
=IF(ROWS(B$1:B1)COUNTA(A$1:A$7),"",INDEX(A:A,SMAL L(IF(A$1:A$7<"",ROW(A$1:A$7)),ROWS(B$1:B1))))

Hmmm...

The only way that's possible is if you have formulas in column A and some of
them return formula blanks "". To account for that change this portion:

COUNTA(A$1:A$7)

To:

COUNTIF(A$1:A$7,"?*")

--
Biff
Microsoft Excel MVP


"James" wrote in message
...
Cool. That worked better than Jacob's. His did not input the first item in
the list.
Although yours gave me #NUM! after the last item. no biggie I guess.

Thanx

"T. Valko" wrote:

Jacob's formula will do that. It could be more robust, though.

Assuming the data range is A1:A7.

Enter this array formula** in B1 and copy down until you get blanks:

=IF(ROWS(B$1:B1)COUNTA(A$1:A$7),"",INDEX(A:A,SMAL L(IF(A$1:A$7<"",ROW(A$1:A$7)),ROWS(B$1:B1))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP

String Formula
 

Is there any specific reason for using =ROWS(B$1:B1)
instead of ROW($B1)

It's more robust and doesn't really "cost" anything efficiency-wise.

If you insert a new row 1 then:

ROW($B1) becomes ROW($B2) and now evaluates as 2.

ROWS(B$1:B1) becomes ROWS(B$2:B2) and *still* evaluates as 1.

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
Thanks Biff.. (I missed the error handling part).

To handle numerics and text we can have the below....
=IF(ROW(A1)<=COUNTIF(A:A,"?*")+COUNT(A:A),INDEX(A: A,SMALL(IF($A$1:$A$100<"",ROW($A$1:$A$100)),ROW(A 1))),"")

One query: Is there any specific reason for using =ROWS(B$1:B1) instead of
ROW($B1)

James:

That worked better than Jacob's. His did not input the first item in the
list.

In the intial post you have mentioned as P2 and hence I have referenced
only
from cell 2 in the formula...

If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

Although yours gave me #NUM! after the last item.
=IF(ROWS(B$1:B1)COUNTA(A$1:A$7),"",INDEX(A:A,SMAL L(IF(A$1:A$7<"",ROW(A$1:A$7)),ROWS(B$1:B1))))

Hmmm...

The only way that's possible is if you have formulas in column A and some
of
them return formula blanks "". To account for that change this portion:

COUNTA(A$1:A$7)

To:

COUNTIF(A$1:A$7,"?*")

--
Biff
Microsoft Excel MVP


"James" wrote in message
...
Cool. That worked better than Jacob's. His did not input the first item
in
the list.
Although yours gave me #NUM! after the last item. no biggie I guess.

Thanx

"T. Valko" wrote:

Jacob's formula will do that. It could be more robust, though.

Assuming the data range is A1:A7.

Enter this array formula** in B1 and copy down until you get blanks:

=IF(ROWS(B$1:B1)COUNTA(A$1:A$7),"",INDEX(A:A,SMAL L(IF(A$1:A$7<"",ROW(A$1:A$7)),ROWS(B$1:B1))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP