This example uses the UNIQUE function. With default settings, UNIQUE will output a list of unique values, i.e. values that appear one or more times in the source data. However, UNIQUE has an optional third argument, called occurs_once that, when set to TRUE, will cause UNIQUE to return only values that appear once in the data.
In the example shown, UNIQUE's arguments are configured like this:
- array - B5:B16
- by_col - FALSE
- occurs_once - TRUE
Because occurs_once is set to TRUE, UNIQUE outputs the 2 values in the data that appear just once: "purple", and "gray".
Notice the by_col argument is optional and defaults to FALSE, so it can be omitted:
TRUE and FALSE can also be replaced with 1 and zero like this:
Dynamic source range
UNIQUE won't automatically change the source range if data is added or deleted. To give UNIQUE a dynamic range that will automatically resize as needed, you can use an Excel Table, or create a dynamic named range with a formula.