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

[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.

 

[ward376]

Try sorting the data.

Cliff Edwards

 

[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.

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

 

[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