This is an advanced formula that requires a number ofnestedfunctions.
However, it is an excellent example of the power ofdynamic array formulas in Excel.
For convenience,datais thenamed rangeB5:B104.
This range contains 100 random color names.
This might impact performance in larger sets of data.
To streamline the formula, we can use theLET function.
The LET function is used to declare and assign values to variables.
Then we replace UNIQUE(data) withuwhere it occurs in the formula.
The result is that UNIQUE values are calculated just one time.
Values can be sorted by one or more columns.
SORT returns a dynamic array of results.
TAKE Function
The Excel TAKE function returns a subset of a given array.
The number of rows and columns to return is provided by separaterowsandcolumnsarguments.
Rows and columns can be extracted from the start or end of the given array.
