Condense out blanks in a list

[Matt]

Say I have a list like the following:

Red
Blue
Green
(blank cell)
Yellow
(blank cell)
Orange
(blank cell)
(blank cell)
Brown

Is there a function I can use (not a filter) that will create a new
list with blanks removed:

Red
Blue
Green
Yellow
Orange
Brown

Any ideas?

Thanks,
Matt

 

[T. Valko]

One way...

Assume your data is in the range A1:A10. The "blank" cells are *EMPTY*
cells.

Try this array formula** :

=IF(ROWS($1:1)<=COUNTA(A$1:A$10),INDEX(A$1:A$10,SMALL(IF(A$1:A$10<>"",ROW(A$1:A$10)-MIN(ROW(A$1:A$10))+1),ROWS($1:1))),"")

Copy down until you get *BLANKS*.

For some, me included, there is a difference between a blank cell and an
empty cell. An empty cell is just that, a cell that contains nothing at all.
A blank cell *can* mean a cell that contains a formula that returns an empty
text string. This is commonly known as a formula blank. the cell looks empty
but it's not because it contains a formula. This is an important
distinction. For example, the above formula will not work properly if the
cells contain formula blanks. This modified version will handle both empty
cells and cells with formula blanks:

Also an array formula** :

=IF(ROWS($1:1)<=ROWS(A$1:A$10)-COUNTBLANK(A$1:A$10),INDEX(A$1:A$10,SMALL(IF(A$1:A$10<>"",ROW(A$1:A$10)-MIN(ROW(A$1:A$10))+1),ROWS($1:1))),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Guest]

Try this:

=IF(ISERR(SMALL(IF(Color<>"",ROW(INDIRECT("1:"&ROWS(Color)))),ROWS($1:1))),"",INDEX(Color,SMALL(IF(Color<>"",ROW(INDIRECT("1:"&ROWS(Color)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed

 

[Roger Govier]

Hi Matt

One way.
Assuming your data is in column A.In column B enter 1 and 2 in B1 and B2,
Mark both cells and copy down for the extent of data in column A.
Mark columns A and B>Data>Sort>Column A
All the blank rows will fall to the end
Delete all rows below your last entry in column A
Mark both columns>Sort>Column B to return to original order.
Delete column B