Last week Keith asked how he can ignore blanks in a range referenced by a Data Validation list.
I think you’ll agree the list below on the right with the blanks removed looks a lot nicer.
Extract a List Excluding Blank Cells
To get the ‘no blanks’ look we first need to create a new list that excludes the blanks.
Here’s our original list containing blanks starting in cell A2 through to A9:
And in column C we’ll create our new list that excludes the blanks.
Stop looking at the formula bar, I don’t want to put you off 🙂
Formula to Extract a List Excluding Blanks
The formula in cell C2 is:
And in English it reads:
Look at the range A2:A10 and return the first value if it is text (i.e. not blank and not a number). If this formula returns an error just enter nothing (as denoted by the "").
This is an array formula so it needs to be entered by pressing CTRL+SHIFT+ENTER, then copy down to remaining rows.
SMALL’s Big Role
Let’s expand on the SMALL(IF(ISTEXT part of the formula first:
The SMALL function syntax is:
We’ve used the IF function to return the array argument. More on that in a moment.
The k argument is the position in the array we want to find. i.e. 1 is the smallest, 2 the next smallest and so on.
You’ll notice in our formula we used ROW(A1) to dynamically return the k argument. i.e. ROW(A1) simply evaluates to 1 and returns the first value in the list that isn’t blank.
We use ROW(A1) so that as we copy the formula down column C the ROW function reference will go up in increments of 1 (because the reference to A1 is relative, not absolute).
So, in cell C3 the ROW function part of the formula will be ROW(A2) which evaluates to 2 and will return the second value in the list that isn’t blank, and so on.
The IF part of the formula first identifies which cells in the range contain text by using the ISTEXT function to test the cells in the range A2:A10.
If the cell does contain text it returns a TRUE, and if not it returns a FALSE, which you can see in orange below:
In the list above the first TRUE is referring to cell A2, the next TRUE refers to A3, and the first FALSE refers to A4 and so on.
Whilst this ISTEXT function is evaluated inside the formula, it might be easier to visualise how it works if we insert the formula in column B like this:
That list of TRUE’s and FALSE’s in column B is the same as the list in the IF formula.
Now, because we actually need a list of cell numbers that contain text (for the SMALL function’s ‘array’ argument), as opposed to TRUE/FALSE values returned by the ISTEXT function, we use ROW($A$1:$A$9) to return an array of numbers 1 through 9 like this:
Note: we need 9 numbers because there are 9 cells in the range A2:A10.
Next the IF function finishes evaluating and returns an array of numbers that represent the cell numbers in the range A2:A10 that contain text like this:
Again, we can see how Excel does this if we put the different components of the formula in separate columns in our workbook:
That is; rows 1,2,4,6,7 and 8 in the range A2:A10 contain text.
Next the SMALL function finishes evaluating and goes from this:
Now finally INDEX knows which value to return from the range A2:A10, which is the first value.
Remember the syntax for INDEX is:
INDEX(reference, row_num, [column_num], [area_num])
Note: We’re only using the first two arguments for INDEX. The column_num and area_num are optional arguments as denoted by the square brackets.
IFERROR - The Fall Guy
IFERROR picks up the pieces if there is an error in the result and simply returns a blank.
This is important because the next thing you need to do is copy this formula down column C to at least row 10 (because later on you might enter values in cells A4, A6 and A10).
However, because there are currently only 6 values in column A you would end up with errors in cells C8:C10 if you don’t use IFERROR.
Download the Workbook
Enter your email address below to download the sample workbook.
Inside the workbook is an option that ignores blanks instead of the example above which relies on finding text.
This is useful if your data isn’t text, or is a combination of text and numbers.
Next week we’ll look at how we can use this list in a Data Validation list that ignores the blank cells at the end. i.e. cells C8:C10 in our example above.