Today, we are going to discuss about the method to extract non-blank cells, when our data with blanks are in multiple columns.
Let's take a look into our requirement.
Here, range A2:D7 contains data with some blanks, our requirement is to extract the non blank cells from that range.
We discussed the same earlier, at that instance, our input is in single column.
Here it is in multiple columns.
Expected result shown in the range F2:F15.
The function we are going use is entirely different to this requirement, but the hidden ability of the functions make it possible. So, lets dive in to see some exciting stuffs.
Let me show you the formula in F2 cell,
=INDIRECT(TEXT(SMALL(IF($A$2:$D$7<>"",ROW($A$2:$D$7)*10^5+COLUMN($A$2:$D$7)),ROWS($I$2:I2)),"R0C00000"),)
If we understand the 'value_if_true' argument in 'IF' function, then we can understand the whole formula. So, let me give some insights about the construction we used in that argument.
Range A2:D7 fed in the argument reference of ROW function, the output of this function will look like this array {2;3;4;5;6;7}. Curly braces says that this is an array and the semicolon is the row delimiter of the array. we multiply this with 10 to the power of 5, then the output will look like this,
Range A2:D7 fed in the argument reference of COLUMN function, the output of this function will look like this array {1,2,3,4}. Curly braces says that this is an array and the Comma is the column delimiter of the array. We do addition operation between two arrays, in which one is the array consists of rows and the other consists of columns. It will pick first element in array1 which is 200000 and then will add each elements of array2 and provide the result as {200001,2000002,2000003,2000004} and will repeat the same steps for 300000 and so on. For you to get some idea, please see below pic,
This is to make you get some idea, we do the same construction in single cell formula using the ROW and COLUMN functions.
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 $A$2:$D$7 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 A2 not equal to blank? in our case the answer is Yes. It has the text "Excel", so the result is TRUE it gives the sequence 200001, then is B2 not equal to blank? B2 cell has the value 11, so the answer is Yes, and so it gives the sequence 200002, is C2 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 D7. The output of IF function will look like below,
This array contains the sequence of number what we have created, but only for the cells which contains data. For non blank cells, we kept it as FALSE and we are going to exclude the FALSE values in SMALL function. This function helps us to pick the 1st small, 2nd small and so on. First small of our array is 200001, we have to find a way to get the row number from the first part of the string and column number from the last part of the string.
To achieve that, we can wrap the output of SMALL function into TEXT function and feed "R0C00000" in format_text argument. if we do that, then the output of text function will look like "R2C00001". Now you can understand that we try to get the R1C1 reference of the cell. Before proceeding further, let me tell you something about R1C1 reference or notation,
Here the cell B3 is selected, and you can see that in name box. The cell is an intersection of Column and Row, here the column is B, and the row is 3. Other method is there in excel to refer the cell with row number and column number.
In that method, this cell can be called as R3C2. Nowadays A1 reference is widely used and so, many of us don't know about this reference.
INDIRECT function in excel can handle this kind of reference.
As the name suggests, INDIRECT function does convert the contents in ref_text argument in to range reference. Let me give you simple example for INDIRECT, before proceeding further.
Here in A3 cell, we have the name 'Meganathan', In B5 cell in the INDIRECT function, we fed 'A3' as the text reference.
So, INDIRECT function converts the text reference into range reference and gets the value from that range. Let's get into our requirement, 2nd argument in this function helps us to pick A1 or R1C1 reference.
If we ignore this argument, by default it will pick A1 reference, otherwise we can give FALSE or 0 or after putting comma, we can simply close the parenthesis. Any way it will consider that as R1C1 reference. So text value of "R2C00001" will get converted in to range reference of R2C1 and then INDIRECT function will pick the value from row 2 column 1 which is 'Excel'.
Comments