removing blank cells in a list

dirtyh2o

New Member
Joined
Oct 11, 2006
Messages
3
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)
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

steve1262

Board Regular
Joined
Aug 19, 2006
Messages
126
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

New Member
Joined
Oct 11, 2006
Messages
3
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. ""
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,054
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top