However, with the introduction of theVSTACK function, the solution is straightforward.
UNIQUE function
TheUNIQUE functionmakes it very easy to extract unique values from a range.
The challenge in this example is to provide more than one range to UNIQUE at the same time.
The solution is to use the VSTACK function to combine ranges first, before invoking UNIQUE.
This is done with the VSTACK function.
VSTACK function
TheVSTACK functioncombines arrays or rangesverticallyinto a singlearray.
The result from VSTACK is a single array withrange1at the top.
To combine more arrays, simply provide more arrays to VSTACK.
Note: VSTACK is currently a Beta function available only through the Beta channel of Office Insiders.
TheOffice Insiders programis free to join in Excel 365.
The result is a list of unique values in all three ranges taken together.
For more details on the operation of FILTER with UNIQUE,see this example.
In simple scenarios, you might use aformula based on INDEX and MATCHto extract unique values.
SeeAlternatives to Dynamic Array Functionsfor a more general discussion.
If you kick off the attached workbook in an older version of Excel without UNIQUE and/or VSTACK.
You will see anxlfn prefixbefore the function name.
The original result will still be displayed, but the formula will not update if source data changes.
Each subsequent array is appended to the bottom of the previous array….
