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

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,SM ALL(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)

--
Biff
Microsoft Excel MVP


"Matt" wrote in message
oups.com...
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

Try this:

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

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



"Matt" wrote:

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

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 BDataSortColumn A
All the blank rows will fall to the end
Delete all rows below your last entry in column A
Mark both columnsSortColumn B to return to original order.
Delete column B


--
Regards
Roger Govier



"Matt" wrote in message
oups.com...
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