Return the non blank items in a list
You can display a list of all the non blank items in a single column by using an array formula.
This single cell formula can be entered in the first cell and then dragged down.
If you are not using Microsoft 365 this formula must be entered with (Ctrl + Shift + Enter).
If you are using Microsoft 365 this formula can be entered without Ctrl + Shift + Enter, thanks to Dynamic Array Formulas.
Cells "A1:A5" displays our list, which includes some blank cells.
Cells "B1:B4" displays our list without the blanks.
Enter the formula into cell "B1" and press Enter.
Drag this cell (and its formula) down to cells B2, B3 and B4.
IF - The value based on whether a certain condition is True or False.
IFERROR - The value or something else if it evaluates to an error.
INDEX - The value from a cell range which is the intersection of a row AND a column.
ISBLANK - The value True or False depending if the value is blank.
NOT - The opposite of a True or False value.
ROW - The row number of a cell reference.
SMALL - The kth smallest value in a list or array of numbers.
Alternative Array Formulas
There are several Different Array Formulas that could be used.
User Defined Function
NONBLANKVALUES - Returns an array of all the non blank items in a range.
© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited Top