How to Use VSTACK and UNIQUE to Consolidate Multi-Region Rosters in Microsoft Excel

Human resources managers, event planners, and project directors frequently manage employee rosters, vendor directories, or attendee lists split across separate worksheets representing regional branches or distinct operating divisions. When consolidating these rosters into a master registry, duplicates often appear where personnel hold cross-regional responsibilities. Combining modern Excel’s VSTACK, UNIQUE, and SORT functions creates an automated, dynamic master roster that deduplicates records in real time.

Understanding the VSTACK, UNIQUE, and SORT Pipeline

Each function provides a specialized transformation step in the consolidation pipeline:

• =VSTACK(array1, array2, ...): Combines multiple independent ranges vertically into a single unified column or table.
• =UNIQUE(array, [by_col], [exactly_once]): Traverses the stacked array and strips out all duplicate entries, preserving only distinct occurrences.
• =SORT(array, [sort_index], [sort_order]): Alphabetizes or numerically orders the deduplicated results for clean executive presentation.

Consolidating Multi-Sheet Regional Personnel Lists

Consider three regional personnel columns located across different tabs: London!A2:A50, NewYork!A2:A50, and Tokyo!A2:A50:

1. Select an empty destination cell on your master consolidation worksheet (such as A2).
2. Enter the nested consolidation expression:

   =SORT(UNIQUE(VSTACK(London!A2:A50, NewYork!A2:A50, Tokyo!A2:A50)))

3. Press Enter. Excel vertically stacks all three regional lists, purges any duplicate employee names that appear across multiple regions, and alphabetizes the final master roster automatically.

Stripping Blank Entries with FILTER

If regional ranges contain empty placeholder rows, UNIQUE retains a blank cell in the results. Remove blank entries using FILTER:

=LET(
    combinedList, VSTACK(London!A2:A50, NewYork!A2:A50, Tokyo!A2:A50),
    cleanList, FILTER(combinedList, combinedList <> ""),
    SORT(UNIQUE(cleanList))
)

Using LET prevents redundant stack calculations and guarantees a continuous, gap-free roster display.

Consolidating Multi-Column Personnel Records

This technique scales seamlessly across multi-column tables (such as Name, Email, and Department):

=SORT(UNIQUE(VSTACK(LondonRoster[#Data], NewYorkRoster[#Data])), 1, 1)

Because Excel structured table references are dynamic, any new employees added to regional tables instantly flow into the master registry without manual intervention.