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

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

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

=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

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

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

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