» Creating a List of All Non-Blank Cells in a Column

Problem:

List1 (column A) contains values as well as blank cells.
We want to create a new list in column B that consists of just the values from List1.

Solution:

Use the INDEX, SMALL, IF, and ROW functions as shown in the following Array formula:
{=INDEX($A$2:$A$11,SMALL(IF($A$2:$A$11<>" ",ROW($A$2:$A$11),1000),ROW()-ROW($B$2)+1)-ROW($B$2)+1)}
Enter the formula in cell B2, and copy it down the column, until the #REF! error is returned.