Unique values from multiple ranges

How to extract unique values from multiple ranges

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:

  1. Open Excel
  2. Type =UNIQUE(VSTACK(B3:B5, B8:B11,D3:D5))
  3. Press Enter
  4. 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:

How to extract unique values from multiple ranges

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.

  1. 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.
  2. 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.

empty cells issue

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:

create a unique list of non-blank values from multiple ranges

Explanation:

  1. 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.
  2. 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.
  3. Finally, the UNIQUE function extracts distinct values from the filtered list, removing duplicates.