» Creating a List of All Non-Blank Cells in a Column
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
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.
Book Store:
Recommended Books:
- The Accounting Game : Basic Accounting Fresh from the Lemonade Stand
- Special Edition Using Microsoft Access 2002
- Accounting and Financial Fundamentals for Nonfinancial Executives
- Good to Great: Why Some Companies Make the Leap... and Others Don't
- Windows XP Pocket Reference
- Finance and Accounting for Nonfinancial Managers
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
