I just noticed another problem. The formula does not return all values in
Column A. For example, if there is data in rows 1:150, data is only returned
in Column B only 2/3 the way through... Thanks for your help.

"Biff" wrote:

Hi!

Try this entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNT(A$1:A$10),INDEX(A$1:A$10,SMA LL(IF(A$1:A$10<"",ROW(A$1:A$10)-ROW(A$1)+1),ROWS($1:1))),"")

Copy down until you blanks.

Biff

"SteveC" wrote in message
...
I would like to convert data in Col A to a format in Col B, so that Col B
omits the blank cells in Col A. Is this possible with a formula I can
drag
down? For example:

Col A Col B
1 1
2
2 34
34 5
5 6
9
6 7
9

7