Unique values case-sensitive

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.