Explanation
This example uses the UNIQUE function together with the FILTER function. You can see a more basic example here .
The trick in this case is to apply criteria to the FILTER function to only allow values based on the count of occurrence. Working from the inside out, this is done with COUNTIF and the FILTER function here:
FILTER(data,COUNTIF(data,data)>1)
The result from COUNTIF is an array of counts like this:
{3;1;3;3;2;1;1;3;1;2;3;3}
which are checked with the logical comparison > 1 to yield an array or TRUE/FALSE values:
{TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE}
Notice TRUE corresponds to values in the data that appear more than once. This array is returned to FILTER as the include argument, used to filter the data. FILTER returns another array as a result:
{"red";"green";"green";"blue";"red";"blue";"red";"green"}
This array is returned directly to the UNIQUE function as the array argument. Notice of the 12 original values, only 8 survive.
UNIQUE then removes duplicates, and returns the final array:
{"red";"green";"blue"}
If values in B5:B16 change, the output will update immediately.
Count > 2
The formula in F5, which lists colors appearing at least 2 times in the source data, is:
=UNIQUE(FILTER(data,COUNTIF(data,data)>2))
Dynamic source range
Because data (B5:B15) is a normal named range, it won’t resize if data is added or deleted. To use a dynamic range that will automatically resize when needed, you can use an Excel Table , or create a dynamic named range with a formula.
Dynamic Array Formulas are available in Office 365 only.
Explanation
In this example, the goal is to create a formula that will extract unique values from a range of data in a case-sensitive way. Normally, we would use the UNIQUE function to extract unique values. However, UNIQUE is not case-sensitive so it won’t work in this situation. One way to solve this problem is to use the REDUCE function with a custom LAMBDA function, as explained below.
REDUCE function
The REDUCE function applies a custom LAMBDA function to each element in a given array and accumulates results to a single value. The generic syntax for the REDUCE function looks like this:
=REDUCE([initial_value], array, lambda)
The calculation performed by REDUCE is determined by a custom LAMBDA function with this generic syntax:
LAMBDA(a,v,calculation)
The first argument, a , is the accumulator. The accumulator begins as the initial_value provided to REDUCE and changes as REDUCE loops over the elements in the array and applies a calculation. The v represents the value of each element in the array. Calculation is the formula logic that creates the final accumulated result.
REDUCE + LAMBDA
In the worksheet shown, the formula in cell D5 is:
=REDUCE(,data,LAMBDA(a,v,IF(SUM(--EXACT(a,v)),a,VSTACK(a,v))))
Notice that the initial value is purposely not provided, because we want to start with a null value. The custom LAMBDA function inside of REDUCE looks like this:
LAMBDA(a,v,IF(SUM(--EXACT(a,v)),a,VSTACK(a,v)))
At a high level, the REDUCE function loops over the values in data one at a time. At each new value, v , the custom LAMBDA function checks if v is already in the accumulator, a . If v is already in a , the current value of a is returned. If v is not already present in a , the function combines a and v with the VSTACK function . The final result is an array that contains case-sensitive unique values.
Details
The EXACT function is what makes this formula case-sensitive, and the IF function is used to test values and control flow:
IF(SUM(--EXACT(a,v)),a,VSTACK(a,v))
The logical_test inside of IF is based on the EXACT function and the SUM function:
SUM(--EXACT(a,v))
Normally, the EXACT function checks if two values are exactly equal, including upper and lowercase characters. In this case, EXACT is comparing a and v . Because a is an array, the result will be an array of TRUE and FALSE values. The double negative (–) is used to convert the TRUE and FALSE values from EXACT into 1s and 0s, which are then summed by the SUM function. If SUM returns a positive number (which evaluates to TRUE in Excel), it means the v already exists in a , and IF returns a . If SUM returns zero (which evaluates to FALSE in Excel), it means v does not yet exist in a , and IF runs the VSTACK function, which is configured to combine a and v by stacking v vertically below a .
