removing blank cells in a list

[dirtyh2o]

Hi, I have a list of data and blank cellls and I would like to make another list that removes the blanks so all that is left is the data. Any idea how to do this?

Thanks!

edit: I'd like this process to be dynamic (I know I could do it by sorting the list using data sort, but that will wind up screwing things up for me down the line)

 

[steve1262]

Need more info, are the cells on a row, column?

So if Cell 5 on Row 5 is blank. What are you looking to do. Delete the entire row?

 

[dirtyh2o]

info is in a column. For example:

in column A:
1. "a"
2. "b"
3. ""
4. ""
5. "c"
6. ""
7. "d"

would like to write an array formula for column B1:B7 that changes column A to:

1. "a"
2. "b"
3. "c"
4. "d"
5. ""
6. ""
7. ""

 

[dirtyh2o]

bump?

 

[erik.van.geit]

Hi,

next formula will only work if there is at least one number in the list (you could add a "dummy" one)

   A    B          
 1 DATA NO EMPTIES 
 2 1    1          
 3 a    a          
 4 b    b          
 5      c          
 6      d          
 7 c    e          
 8                 
 9 d               
10 e               

Blad1
[Table-It] version 06 by Erik Van Geit

RANGE   FORMULA (1st cell)
B2:B7   {=IF(COUNT($A$2:$A$14)<ROW()-ROW(A2)+1,"",INDEX($A$2:$A$14,SMALL(IF($A$2:$A$14<>"",ROW($A$2:$A$14)-ROW(A$2)+1),ROW()-ROW(A$2)+1)))}

{=formula}:
 select first cell
 enter formula without {}
 confirm with Control-Shift-Enter
 then copy down & across

[Table-It] version 06 by Erik Van Geit

an intresting thread: VBA or formulasystem
http://www.mrexcel.com/board2/viewtopic.php?p=1019894

kind regards,
Erik