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)
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

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
 

Forum statistics

Threads
1,141,218
Messages
5,705,080
Members
421,377
Latest member
FerdiFuchs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top