To extract unique values from multiple ranges, you can apply a formula that uses the UNIQUE function with the VSTACK function.
How to extract unique values from multiple ranges
Follow the steps below to get the unique values from multiple ranges:
- Open Excel
- Type =UNIQUE(VSTACK(B3:B5, B8:B11,D3:D5))
- Press Enter
- This formula will stack all values from the specified ranges and return only the unique values.
Example
In the example, we have three ranges. The goal is to create a modern formula to combine data into a single range and get the unique values from the three lists.
Formula:
=UNIQUE(VSTACK(B3:B5, B8:B11,D3:D5))
Result:
Now, we have a single-column list in the range F3:F7. The formula effectively removed the duplicates like Apple and Banana.
Explanation
Take a closer look at the formula.
- VSTACK(B3:B8,…): The VSTACK function vertically stacks or combines the ranges B3:B5, B8:B11, and D3:D5 into a single column of values. This combined list will contain all values from these ranges, with duplicates included at this stage.
- UNIQUE(…): The UNIQUE function filters out duplicate entries from the stacked list, returning only distinct values.
This formula creates a unique list of values from multiple ranges in a single column.
Handling Empty cells
Sometimes, we have to work with non-contiguous ranges. Let us see the possible workaround.
Formula:
=UNIQUE(FILTER(VSTACK(B3:B6, B9:B13, D3:D6), VSTACK(B3:B6, B9:B13, D3:D6) <> “”))
The formula is now correctly set up to create a unique list of non-blank values from multiple ranges.
Result:
Explanation:
- VSTACK consolidates the ranges B3:B6, B9:B13, and D3:D6 into a single column of values. This step is essential to prepare a unified list for filtering and removing duplicate items.
- The FILTER function uses a condition to exclude any blank cells from the stacked list. The condition VSTACK(B3:B6, B9:B13, D3:D6) <> “” tells Excel to retain only the non-blank entries.
- Finally, the UNIQUE function extracts distinct values from the filtered list, removing duplicates.
Related formulas
- Count unique text values in a range
- Count unique values and distinct values
- Unique values with criteria
- Count values in multiple ranges with criteria