This example uses the UNIQUE function together with the FILTER function. Working from the inside out, the FILTER function is first used to remove any blank values from the data:
The <> symbol is a logical operator that means "does not equal". For more examples of operators in formula criteria see this page.
FILTER returns an array of values, excluding empty strings:
This array is returned directly to the UNIQUE function as the array argument. UNIQUE then removes duplicates, and returns the final array:
UNIQUE and FILTER are dynamic functions. If data in B5:B16 changes, the output will update immediately.
Dynamic source range
Because the range B5:B15 is hardcoded directly into the formula, it won't change if data is added or deleted. To use a dynamic range that will automatically resize as needed, you can use an Excel Table, or create a dynamic named range with a formula.