|  

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


Rate This Tip
12 34 5
Rating: 3.10     Views: 16827
how to make this formula index a longer list
Eieans
How do you make this formula look at a longer list in column A? I tried to make my list from A2:A16 , but I get NUM! errors in column B
Click here to post comment
For Registered Users
Name
Comment Title
Comments