where data is the named range B5:D15. The result is a list of unique pairs of color and region, sorted by color. Working from the inside out, the FILTER function is used to filter out the middle column, “Qty”, like this: The array constant {1,0,1} is what does the actual filtering, and notice this is a horizontal array, separated by commas. The result from FILTER is a two-dimensional array with 2 columns and 11 rows like this: Notice data in the “Qty” column has been removed. This array is delivered to the UNIQUE function, which can automatically extract unique values from data with adjacent columns. The UNIQUE function returns a smaller two-dimensional array with 2 columns and 5 rows like this: Notice this array contains only the unique combinations of Color and Region. This smaller array is then handed off to the SORT function, which returns the same data, sorted by Color, as seen in the example. The SORT function is optional and can be removed. This is a nice example of nesting one function inside another. When you see a formula created this way, learn to read from the inside out. The inner functions deliver values to the outer functions. The outermost function returns the final result.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
