Today, I will demonstrate the effectiveness of Dynamic Array Functions using two examples.
Example 1:
The first example is to obtain a sorted list of unique items.
In Excel 2016 or earlier versions, if you wanted to create a sorted list of unique values,
I would show you one way to do this.
=INDEX($B$4:$B$24,INDEX(MODE.MULT(IFERROR(MATCH(ROW($B$4:$B$24)-ROW($B$4)+1,MMULT(--($B$4:$B$24>=TRANSPOSE($B$4:$B$24)),ROW($B$4:$B$24)^0),{0,0}),"")),ROWS($D$4:D4)))
It can be quite complex; you must spend time understanding the structure of this formula.
These methods may require the special keystroke CTRL + SHIFT + ENTER, which will add curly braces around our formula.
If someone who is less familiar with array formulas attempts to edit by clicking inside the cell and pressing ENTER, the formula will produce an error or, in some cases, due to implicit intersection, yield incorrect results.
However, with Dynamic Array functions, the process becomes much easier:
=SORT(UNIQUE(B4:B24))
It's that straightforward. The formula is significantly shorter, enhancing efficiency.
It’s simple to understand, and you don't have to copy the formula down the column.
Example 2:
The task for today involves extracting non-blank cells.
We start with a list of names that includes some empty cells.
I will illustrate one approach using Excel 2016 or earlier version.
In Cell D4, use the following formula:
=IF(ROWS($D$4:D4)>$E$1,"",INDEX($B$4:$B$21,SMALL(IF($B$4:$B$21<>"",ROW($B$4:$B$21)-ROW($B$4)+1),ROWS($D$4:D4))))
For Cell E1, the formula is:
=COUNTA(B4:B21)
This method requires the special keystroke combination CTRL + SHIFT + ENTER, and we need to drag the formula down to include additional rows for new data. The IF function can be used for error handling, as it is often more effective than the IFERROR function.
However, with the introduction of Dynamic Array functions, this process becomes even simpler.
Method-1:
=TOCOL(B4:B21,1)
Method-2:
=LET(a,B4:B21,FILTER(a,a>""))
These methods are elegant, efficient, and easy to understand.
Comments