Hi - Dave here.
The UNIQUE function makes it incredible easy to extract unique values from a range. Just provide a range, and UNIQUE will give you back the unique values:
=UNIQUE(range)
But how do you extract UNIQUE values from more than one range at the same time? Up to now, this has been a difficult problem, since UNIQUE is programmed to accept only one array. However, with the introduction of the VSTACK function, the solution is simple. In the worksheet below, the formula in cell H5 is:
=UNIQUE(VSTACK(range1,range2,range3))
The VSTACK function combines the three ranges by stacking them vertically and returns the result to UNIQUE. The UNIQUE function then returns the unique values from the single combined range.
[
download the workbook and read the full explanation]
This is a great example of the future of Excel formulas — much simpler solutions that leverage the new dynamic array engine in Excel 365. The VSTACK function is currently in Beta, available in the Beta channel of Office Insiders. The article includes a link for more information. The worksheet is also attached to the article.
Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a nice weekend!
Dave
