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