» Creating a List of All Non-Blank Cells in a Column
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
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.
Book Store:
Recommended Books:
- Financial Shenanigans : How to Detect Accounting Gimmicks & Fraud in Financial Reports
- Mastering Excel 2000 (for beginner)
- Cashflow Quadrant: Rich Dad's Guide to Financial Freedom
- Business Plans Kit for Dummies (With CD-ROM)
- Positioning: The Battle for Your Mind
- Essentials of Investments with Standard & Poor's Educational Version of Market Insight + PowerWeb + Stock Trak Coupon
No comments have been submitted.

