How can I put out an empty cells of large list in Excel?

A

Ali Reza

Hi
I have a large column with empty cells and cells with entry.
I want to collect cells with entry in a new smaller column and put out the
empty cells.
Please help me.
 
L

Lars-Åke Aspelin

On Fri, 8 Aug 2008 23:53:01 -0700, Ali Reza <Ali
Hi
I have a large column with empty cells and cells with entry.
I want to collect cells with entry in a new smaller column and put out the
empty cells.
Please help me.
Click to expand...


If you data is in column A from row 1 to row 100, try the following
formula in cell B1


=IF(ROW()>COUNTA(A$1:A$100),"",INDEX(A$1:A$100,SMALL(NOT(ISBLANK(A$1:A$100))*ROW(A$1:A$100),ROW()+ROWS(A$1:A$100)-COUNTA(A$1:A$100,""))))

If you have your list in a named range, rng, you can make this a
little shorter like

=IF(ROW()>COUNTA(rng),"",INDEX(rng,SMALL(NOT(ISBLANK(rng))*ROW(rng),ROW()+ROWS(rng)-COUNTA(rng))))

Note: This is an array formula that has to be entered with
CTRL+SHIFT+ENTER rather than just ENTER

Copy the formula down in column B as far as needed.

If you want the resulting list to start at some other place, say in
cell F5 rather than B1, just modify the formula by changing ROW() to
ROW()-ROW(F$5) +1 in the two places where it is.

Hope this helps / Lars-Åke
 
G

Gord Dibben

If the cells are truly empty...............

Select the column and F5>Special>Blanks>OK

Edit>Delete>Shift Up

You could do this on a copy of the column if you want to preserve the
original.


Gord Dibben MS Excel MVP

On Fri, 8 Aug 2008 23:53:01 -0700, Ali Reza <Ali
 
Top