Delete blanks and errors in a list
Table of Contents
1. 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.
Get excel *.xls
2. 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.
Instructions:
- Go to "Home" tab
- Press with left mouse button on "Find & Select"
- Press with left mouse button on "Go to Special..."
- Press with left mouse button on "Formulas"
- Enable "Errors"
- Press with left mouse button on ok!
If any formula errors exist, they are now selected. The picture below demonstrates error cells being selected.
Blank cells category
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]
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 […]
Excel categories
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!