Delete blanks and errors in a list
The formula deletes blank cells and cells with errors. It doesn't matter if the cells contain numbers or text, they all will be presented in a new column.
Array formula in cell D3:
How to create an array formula
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. See picture below.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
How to copy array formula
- Copy (Ctrl + c) cell D3
- Paste (Ctrl + v) array formula on cell range D3:D11
Explaining formula in cell D3
Step 1 - Identify blank cells
The ISBLANK function returns TRUE if cell is blank (empty) and FALSE if not.
ISBLANK($B$3:$B$20)
becomes
ISBLANK({2; 4; 0; 3; "AA"; 0; "CC"; 0; 5; #REF!; 0; #DIV/0!; 0; "ZZ"; "DD"; 0; 7; #NUM!})
and returns
{FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE}
Step 2 - Identify errors
The ISERROR function returns TRUE if cell contains an error and FALSE if not.
ISERROR($B$3:$B$20)
becomes
ISERROR({2; 4; 0; 3; "AA"; 0; "CC"; 0; 5; #REF!; 0; #DIV/0!; 0; "ZZ"; "DD"; 0; 7; #NUM!})
and returns
{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}
Step 3 - Add arrays
If at least one of the boolean values is TRUE then the result must be TRUE, addition is what we need to use.
Boolean | Boolean | Multiply | Add |
FALSE | FALSE | 0 (zero) | 0 (zero) |
FALSE | TRUE | 0 (zero) | 1 |
TRUE | TRUE | 1 | 2 |
ISBLANK($B$3:$B$20)+ISERROR($B$3:$B$20)
becomes
{FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE} + {FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}
returns {0; 0; 1; 0; 0; 1; 0; 1; 0; 1; 1; 1; 1; 0; 0; 1; 0; 1}.
Step 4 - Convert array to row numbers
The IF function lets you use a logical expression to determine which value (argument) to return.
IF(ISBLANK($B$3:$B$20)+ISERROR($B$3:$B$20), "", MATCH(ROW($B$3:$B$20),ROW($B$3:$B$20)))
becomes
IF({0; 0; 1; 0; 0; 1; 0; 1; 0; 1; 1; 1; 1; 0; 0; 1; 0; 1}, "", {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18})
and returns
{1;2;"";4;5;"";7;"";9;"";"";"";"";14;15;"";17;""}
Step 5 - Get k-th smallest row number
To be able to return a single value from the array we need to use the SMALL function to extract a single row number. The second argument in the SMALL function uses the ROWS function with an expanding cell reference to extract a new value in each cell.
SMALL(IF(ISBLANK($B$3:$B$20)+ISERROR($B$3:$B$20), "", MATCH(ROW($B$3:$B$20),ROW($B$3:$B$20))), ROWS($A$1:A1))
becomes
SMALL({1;2;"";4;5;"";7;"";9;"";"";"";"";14;15;"";17;""}, ROWS($A$1:A1))
becomes
SMALL({1;2;"";4;5;"";7;"";9;"";"";"";"";14;15;"";17;""}, 1)
and returns 1.
Step 6 - Return value based on row number
The INDEX function returns a value from a cell range based on a row and column number, our cell range is a single column so we need to only specify a row number in order to get the correct value.
INDEX($B$3:$B$20, SMALL(IF(ISBLANK($B$3:$B$20)+ISERROR($B$3:$B$20), "", MATCH(ROW($B$3:$B$20),ROW($B$3:$B$20))), ROWS($A$1:A1)))
becomes
INDEX($B$3:$B$20, 1)
becomes
INDEX({2;4;0;3;"AA";0;"CC";0;5;#REF!;0;#DIV/0!;0;"ZZ";"DD";0;7;#NUM!}, 1)
and returns 2 in cell D3.
Download excel *.xls
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]
How to find errors in a worksheet
Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]
In this article, I am going to show you two ways on how to find blank cells. Both techniques are […]
How to quickly select blank cells
In this smaller example, column D (Category) has empty cells, shown in the picture above. If your column contains thousands of […]
2 Responses to “Delete blanks and errors in a list”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Thank you! This is my favorite excel formula so far.
Hi , I'm having trouble getting your formula to work properly in my sheet. Your formula works fine in your sheet but when I copy it into mine I cannot get **** excel to copy it while incrementing the row1:1 portion of the formula. I can copy the first cell identically into all cells in the column but that is no good. If I try to edit it I just get an error telling me I can't edit an array fomula. If I try to use "fill down" then the first cell works fine but all the others in the column return NUM or VAL errors! How the flip do I get **** excel to copy the formula down a column while incrementing the ROW1:1 to ROW2:2 etc?
This is driving me nuts!
Running windoze 10 home with whichever version of excel that shipped with.
Your formula does pretty much exactly what I need which is great- if I can just get it to increment those row numbers!
Help gratefully received!