Lookup & Reference
Extract unique values across multiple ranges while ignoring blanks (VSTACK, FILTER & UNIQUE)
Combine two lists, remove empty entries while retaining numeric zero, and produce a sorted distinct list and count.
Why bare UNIQUE(VSTACK(rng1, rng2)) leaks a bogus 0—and how FILTER fixes it!
A blank-selection predicate must distinguish empty content from valid numeric zero. This example constructs a mask with 1-COUNTBLANK for each source cell before filtering the stacked list. Formula results equal to an empty string are treated as empty.
UNIQUE removes duplicates using the spreadsheet engine's equality rules; this is not a case-sensitive identifier audit or whitespace normalization. Spaces are retained. The joined result is a display string: commas inside source values are not escaped as CSV.
An entirely empty input returns an empty display string and count zero. It must not be counted as one unique blank or surface a no-match error. To extend these fixed eight cells, extend both the VSTACK source and its corresponding mask.
| Range 1: Q1 Regions (A2:A5) | Range 2: Q2 Regions (C2:C5) | Stacked Non-Blank Pool | Sorted Unique Output |
|---|---|---|---|
| North | East | 6 non-blank entries | 1. East |
| "" (Blank) | West | (2 blanks stripped!) | 2. North |
| West | "" (Blank) | Duplicates: North, West | 3. South |
| North | South | 4 distinct regions | 4. West |
Sorted unique non-blank values from two (or more) ranges
The numeric mask contains one item per source row. FILTER keeps populated entries, UNIQUE deduplicates and SORT orders the resulting list. TEXTJOIN is only the display layer; the following section provides the actual spill formula.
=TEXTJOIN(", ",TRUE,SORT(UNIQUE(FILTER(VSTACK(A2:A5,C2:C5),VSTACK(1-COUNTBLANK(A2),1-COUNTBLANK(A3),1-COUNTBLANK(A4),1-COUNTBLANK(A5),1-COUNTBLANK(C2),1-COUNTBLANK(C3),1-COUNTBLANK(C4),1-COUNTBLANK(C5)),""))))Count distinct non-blank items across multiple ranges
First detect an empty mask, then count rows of the distinct filtered array. This avoids counting the empty FILTER fallback as a real item.
=IF(SUM(VSTACK(1-COUNTBLANK(A2),1-COUNTBLANK(A3),1-COUNTBLANK(A4),1-COUNTBLANK(A5),1-COUNTBLANK(C2),1-COUNTBLANK(C3),1-COUNTBLANK(C4),1-COUNTBLANK(C5)))=0,0,ROWS(UNIQUE(FILTER(VSTACK(A2:A5,C2:C5),VSTACK(1-COUNTBLANK(A2),1-COUNTBLANK(A3),1-COUNTBLANK(A4),1-COUNTBLANK(A5),1-COUNTBLANK(C2),1-COUNTBLANK(C3),1-COUNTBLANK(C4),1-COUNTBLANK(C5)),""))))Spill the actual list into cells
Use this formula when downstream calculations need separate cells instead of a comma-separated label. Leave the destination cells empty. An empty source displays one empty fallback cell, while the distinct count remains zero.
=SORT(UNIQUE(FILTER(VSTACK(A2:A5,C2:C5),VSTACK(1-COUNTBLANK(A2),1-COUNTBLANK(A3),1-COUNTBLANK(A4),1-COUNTBLANK(A5),1-COUNTBLANK(C2),1-COUNTBLANK(C3),1-COUNTBLANK(C4),1-COUNTBLANK(C5)),"")))Try the example
Use the original values or change them to test your understanding.