Pete

Bernard posted this a couple hours ago in another thread.

=INDEX($A$1:$A$1000,SMALL(IF($A$1:$A$1000<"",ROW( $A$1:$A$1000)),ROWS($A$1:A1)))

Array formula so use crtl + shift + enter then copy down until you get
error.


Gord Dibben MS Excel MVP


On Thu, 18 Jun 2009 08:48:01 -0700, Pete J <Pete
wrote:

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