Use formula to remove blank entries from a column
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,M IN(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
---------------
Jacob Skaria
"Pete J" 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
|