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

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.

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

Try sorting the data.

Cliff Edwards

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

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

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,SMA LL(NOT(ISBLANK(A$1:A$100))*ROW(A$1:A$100),ROW()+RO WS(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(ISBLA NK(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

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

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

Select the column and F5SpecialBlanksOK

EditDeleteShift 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
wrote:

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.