How to combine two lists without duplicates in Excel
Formula (Excel and Google Sheets)
=UNIQUE(VSTACK(A2:A7,C2:C8))Stack the lists with VSTACK and remove the repeats with UNIQUE: =UNIQUE(VSTACK(A2:A7,C2:C8)).
- Result on the example
- a 9-row list that spills from E2 in
E2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
VSTACK puts the second list under the first, giving one column of 13 addresses. UNIQUE then keeps the first copy of each, so people on both lists appear once. Both steps are live: add an address to either list (inside the ranges) and the combined list updates.
=UNIQUE(VSTACK(A2:A7,C2:C8))Result{9 rows × 1 col} "ana@ruiz.example", "raj@patel.example", "lee@chen.example", "kim@ortiz.example", …
A2:A76 rows × 1 col: "ana@ruiz.example", "raj@patel.example", "lee@chen.example", "kim@ortiz.example", …A range: 6 cells in one column.
C2:C87 rows × 1 col: "lee@chen.example", "ana@ruiz.example", "mo@diaz.example", "eve@lund.example", …A range: 7 cells in one column.
VSTACK(A2:A7,C2:C8){13 rows × 1 col} "ana@ruiz.example", "raj@patel.example", "lee@chen.example", "kim@ortiz.example", …VSTACK: Stacks ranges on top of each other.- array1
A2:A76 rows × 1 col: "ana@ruiz.example", "raj@patel.example", "lee@chen.example", "kim@ortiz.example", … - array2
C2:C87 rows × 1 col: "lee@chen.example", "ana@ruiz.example", "mo@diaz.example", "eve@lund.example", …
- array1
UNIQUE(VSTACK(A2:A7,C2:C8)){9 rows × 1 col} "ana@ruiz.example", "raj@patel.example", "lee@chen.example", "kim@ortiz.example", …UNIQUE: Distinct rows of a range. Spills.- array
VSTACK(A2:A7,C2:C8){13 rows × 1 col} "ana@ruiz.example", "raj@patel.example", "lee@chen.example", "kim@ortiz.example", …
- array
In Google Sheets
The same formula works in Google Sheets: =UNIQUE(VSTACK(A2:A7,C2:C8)). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 9 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SORT(UNIQUE(VSTACK(A2:A7,C2:C8))) | spills 9 × 1Different result | Sort the combined list A to Z. Alphabetical order, starting with ana@ruiz.example and ending with sam@cole.example. |
| =UNIQUE({A2:A7;C2:C8})Google Sheets | spills 9 × 1 | In Google Sheets, an array with a semicolon stacks ranges too, and works in older sheets that predate VSTACK. |
Common mistakes
- Empty cells in the ranges add a 0 to the list. Use
TOCOL(VSTACK(A2:A20,C2:C20),1)inside UNIQUE to drop them when the ranges run past the data. - "Ana@ruiz.example" and "ana@ruiz.example" count as the same value, but a trailing space doesn't. Clean emails with TRIM and LOWER first.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Combine two lists without duplicates
E2, combine the email lists in A2:A7 and C2:C8 into one list with each address once.Questions
How do I combine three lists?
Add the range to VSTACK: =UNIQUE(VSTACK(A2:A7,C2:C8,E2:E9)).
How do I combine lists from two sheets?
Use sheet references inside VSTACK: =UNIQUE(VSTACK(Sheet1!A2:A50,Sheet2!A2:A50)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.