Use formula to remove blank entries from a column

[Pete J]

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

 

[Jacob Skaria]

If you dont have duplicate entries; try this..Please note that this is an
array formula. Within the cell in edit mode (F2) paste this formula and press
Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you
can notice the curly braces at both ends like "{=<formula>}"

Your data in Col A..
B1
=INDEX(A1:A100,MIN(IF(A1:A100="","",ROW(A1:A100))))

B2 (all in one line)
=IF(COUNTA($A$1:$A$100)>=ROW(),INDEX($A$1:$A$100,MIN(IF(INDIRECT("A" &
MATCH(B1,$A$1:$A$100,0)+1 &":A100")="","",ROW(INDIRECT("A" &
MATCH(B1,$A$1:$A$100,0)+1 & ":A100"))))),"")

and copy that down as required...

If this post helps click Yes

 

[John Bundy]

You won't accomplish that with just a formula. You can do it easily with a
number of different macros, but they all involve a method you didn't want to
do.

 

[Pete J]

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

 

[Gord Dibben]

John

If I was rating your answer I would give it a low rating.

There are several ways to do this using a formula.


Gord Dibben MS Excel MVP

 

[T. Valko]

It's usually a bad idea to say that something can't be done. I know this
from my own experience! <BG>

 

[Gord Dibben]

Oh yeah!

I know all about that<g>

But I don't ask to be rated..........when the readers decide I'm full of
s**t they will let me know by ignoring my posts.

Is that why I don't see many responses?


Gord