Today, we are going to discuss about various methods to extract non-blank cells, when our data with blanks are in single column.
Let's take a look into our requirement!
Our data with blanks is in range B3:B15,
Our desired result is in the range D3:D11.
Here, the requirement is pretty simple and straight forward, remove the blanks from the given data range and extract the remaining data.
We can do this task in multiple ways in excel, but here we are going to discuss about 2 methods with the help of INDEX, SMALL functions and INDEX, AGGREGATE functions.
In both methods, we are going to get the relative position of cells containing values,
In that way we are going to eliminate the blank cells.
Method 1:
Formula in D3 cell is,
=INDEX($B$3:$B$15,SMALL(IF($B$3:$B$15<>"",ROW($B$3:$B$15)-ROW($B$3)+1),ROWS($D$3:D3)))
Consider that we put sequence of numbers in our data as shown below, the relative positions we need to pick is {1,2,4,6,7,8,9,11,13}, that's where the range contains values, other cells are blank, so we don't need them.
So, what we have to do here is 2 things.
First, we have to find the way to get sequence of numbers from 1 to 13 and then from that sequence of numbers we should extract the relative position of the cells which contains values.
To create the sequence of numbers in robust form, we are going to use ROW function, If we feed the range B3:B15 in the reference argument of row function, it will deliver the row numbers of that range in the array form as shown below,
To evaluate the formula, select the portion you want to evaluate and press F9, press ESC after checking, otherwise it will hardcode the numbers.
Our requirement is to start the sequence from 1 and so we are detecting the row number of range $B$3 which is 3, if we detect by 3 then sequence will start from 0 and so we are adding 1.
Now we should exclude the relative positions of blank cells from the range B3:B15 and so we wrap this sequence of numbers in IF function.
In the IF function, logical_test argument, we use the comparative operator "not equal to"
(<>), when we do comparison there has to be 2 sides, here one side is the range $B$3:$B$15 and the other side is Zero length text string ("") which means blank. So, the question we are asking in logical_test argument is this, is B3 not equal to blank? in our case the answer is Yes. It has the text "K", so the result is TRUE it gives the sequence 1, then is B4 not equal to blank? B4 cell has the value 11, so the answer is Yes, and so it gives the sequence 2, is B5 not equal to blank? the answer is No, so the result is FALSE, [value_if_false] argument is omitted and so, by default it will deliver FALSE. In the same way it will ask the question still the cell B15.
If we evaluate the array argument in SMALL function, the result will look like below,
Now, we are going to wrap this array in SMALL function in array argument,
Small function is programmed to ignore texts and boolean values, so if we ignore the FALSE values, the result will look like {1;2;4;6;7;8;9;11;13}.
'K' argument is used to pick the nth small in array. Here we use ROWS function with expandable range $D$3:D3, in the first reference both row and column is locked, so it is absolute reference. This reference will not move as we copy the formula down. But the 2nd reference is not locked in both row and column and so it is relative reference, it will move the reference as we copy the formula down.
So, in the first cell this will deliver 1 and as we drag the formula down it will increment by 1. In that way we can use this function to fulfill the requirement of 'K' argument. In our array 1st small is 1, 2nd small is 2, 3rd small is 4 and so on. Now we are going to wrap our range B3:B15 into array argument in INDEX function and the output of SMALL function in row_num argument.
Method 2:
In this method we are going to get help from AGGREGATE function.
The formula in D3 cell is,
=INDEX($B$3:$B$15,AGGREGATE(15,6,(ROW($B$3:$B$15)-ROW($B$3)+1)/($B$3:$B$15<>""),ROWS($D$3:D3)))
Let me deconstruct the AGGREGATE part, which is the core of this function.
Arguments of this function are,
=AGGREGATE(function_num,options,array,[k])
Argument - function_num:
This argument allows us to do 19 aggregate operations with list box facility to select desired operation as shown. Operations from 10 to 19 can handle arrays. If we drag the scroll bar down, we can see the options for large and small operations. The very same SMALL function which we discussed in our previous method, but here with much more ammunition. Option number for SMALL function is 15.
Argument - options:
This argument allows us to select 8 options.
In the next argument we will feed range or array, in that range or array, we have that option to ignore hidden rows, error values or subtotal function. Since, our construction in array argument will have error values for the records other than the matching records. we are going to use option 6.
Argument - array:
In the Array argument, we have two parts, numerator and denominator.
In the numerator part,
we can see that the same construction which we used in our previous method to create the sequence of numbers.
In the denominator part,
we simply use the logical operation ($B$3:$B$15<>""), we used the same in IF function in earlier method.
What this operation does is that, it will check each cell in the range from B3 to B15, are you not equal to blank. If yes, then it will deliver TRUE else FALSE.
After evaluation, the denominator looks like below,
When we do mathematical operation, TRUE will be converted into1 and FALSE will be converted into 0, here we are doing division operation, when we divide any number by 1, the result will be same, if we divide any number by 0, it will throw an error, so the output of array argument in AGGREGATE function will look like below,
option 6 in the options argument will exclude the errors and function_num 15 is the replacement of SMALL function. Argument'K' is used to get the nth small.
As discussed in the 1st method, by using ROWS function we can extract 1st, 2nd, 3rd small and so on.
Difference between the two method is that the construction of array based on the capability of SMALL and AGGREGATE functions.
Comments