Thanks everyone. I tried all the ideas here, and the one that seems to work
best for me is this one. Problem solved!

"T. Valko" wrote:

Try this:

Data in the range Sheet1 A1:A1000

On Sheet2 enter this formula in A1:

=COUNTA(Sheet1!A:A)

That will return the count of non-empty cells from Sheet1 A1:A1000.

Enter this array formula in A2:

=IF(ROWS(A$2:A2)<=A$1,INDEX(Sheet1!A$1:A$1000,SMAL L(IF(Sheet1!A$1:A$1000<"",ROW(Sheet1!A$1:A$1000)) ,ROWS(A$2:A2))-ROW(Sheet1!A$1)+1),"")

** 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.

Copy down until you get blanks. If the number of non-empty cells varies then
you'll have to copy to a range big enough to ensure you get all the expected
results. Only you know how many results you typically expect.

--
Biff
Microsoft Excel MVP


"Pete J" <Pete wrote in message
...
I have a single column of data that is sparse, in that many of the entries
are blank. Since this data is imported from another source, I want to
operate
on it with a formula and list the non-blank entries on another page. Thus
if
there are 1000 lines of data, but only 20 of them are non-blank, the new
column would have just the 20 non-blank entries listed, with no blank
lines
in between.

I don't want to use filters or sorting, since this needs to be an
automatic
process once the data is updated. I suspect I can do this with the index
function and treat the column as an array, but I just can't figure out the
right formula.

Any ideas?

Thanks,

Pete