Do you know a way in Excel to "calculate" by formula a list of unique values ?
E.g: a vertical range contains values "red", "blue", "red", "green", "blue", "black"
and I want to have as result "red, "blue", "green", "black" + eventually 2 other blank cells.
I already found a way to get a calculated sorted list using SMALL or LARGE combined with INDEX, but I'd like to have this calculated sort as well, WITHOUT USING VBA.
Since fall 2018, the subscription versions of Microsoft Excel (Office 365 / Microsoft 365 app) contain so called dynamic array functions (not yet available in Office 2016/2019 nonsubscription versions).
One of those functions is the UNIQUE function that will deliver an array of unique values for the selected range.
Example
In the following example, the input values are in range A1:A6. The UNIQUE function is typed into cell C1.
=UNIQUE(A1:A6)
As you can see, the UNIQUE function will automatically spill over the necessary range of cells in order to show all unique values. This is indicated by the thin, blue frame around C1:C4.
As the UNIQUE function automatically spills over the necessary number of rows, you should leave enough space under the C1. If there is not enough space, you will get a #SPILL error.
If you want to reference the results of the UNIQUE function, you can just reference the cell containing the UNIQUE function and add a hash # sign.
=C1#
It is also possible to check unique values in several columns. In this case, the UNIQUE function will deliver all rows where the combination of the cells within the row are unique:
If you wish to show unique columns instead of unique rows, you have to set the [by_col] argument to TRUE (default is FALSE, meaning you will receive unique rows).
You can also show values that appear exactly once by setting the [exactly_once] argument to TRUE:
=UNIQUE(A1:A6;;TRUE)
