Excel Formulas to Combine Two Columns and Remove Duplicates

Manually consolidating data in Excel while removing redundant entries is a tedious, error-prone struggle for busy professionals. When reconciling tracking sheets for standard funding sources, such as departmental allocations and capital reserves, raw data often overlaps. Implementing a dynamic array formula grants users an automated, deduplicated master list, saving hours of manual cleanup. However, this approach carries the stipulation that your system must run Excel 365 or 2021 to support modern engine functions. For instance, merging "Federal Grants" and "Private Endowments" columns instantly yields a unified, clean registry. Below, we break down the exact formula to streamline your workflow.

Data cleaning is one of the most common tasks in Excel, and merging datasets from different sources is a frequent challenge. Often, you will find yourself with two separate columns of data-such as customer lists, product codes, or email addresses-that you need to consolidate into a single, master list. The catch? You need to combine them while automatically skipping any duplicate values.

Depending on your version of Excel and your specific data layout, there are several ways to accomplish this. In this comprehensive guide, we will explore the best formulas and techniques to combine two columns and skip duplicates, ranging from modern dynamic array formulas to legacy methods and Power Query solutions.

Understanding the Two Scenarios of "Combining Columns"

Before writing our formulas, we must clarify what "combining" means in your specific case. There are two primary scenarios:

• Scenario A (Vertical Consolidation): You want to stack Column A and Column B into a single, long column containing only unique values.
• Scenario B (Horizontal/Row-by-Row Concatenation): You want to merge the values of Column A and Column B side-by-side in each row (e.g., combining "Apple" and "Apple" into just "Apple", or "Apple" and "Banana" into "Apple, Banana").

We will cover step-by-step solutions for both scenarios below.

Method 1: The Modern Excel Way (Vertical Stack & Unique)

If you are using Microsoft 365 or Excel 2021/2024, you have access to dynamic array formulas. This makes combining columns incredibly simple and completely dynamic. If your source data changes, your combined list will update automatically.

The Formula

To combine Column A (A2:A10) and Column B (B2:B10) vertically and skip duplicates, enter the following formula in an empty column:

=UNIQUE(VSTACK(A2:A10, B2:B10))

How It Works

• VSTACK(A2:A10, B2:B10): This function stands for "Vertical Stack." It takes the two specified ranges and appends one on top of the other, creating a single virtual array.
• UNIQUE(...): This function wraps around the stacked array, evaluating the merged list and extracting only the unique values, discarding any duplicates.

Handling Blank Cells

If your source columns contain empty cells, the VSTACK function will interpret those blanks as zeros (0). To ignore blank cells and keep your final list clean, you can integrate the FILTER function:

=UNIQUE(FILTER(VSTACK(A2:A10, B2:B10), VSTACK(A2:A10, B2:B10) <> ""))

This advanced formula ensures that only non-empty values are stacked and evaluated for uniqueness, preventing an unwanted "0" or blank row from appearing in your final output.

Method 2: Row-by-Row Concatenation Skipping Duplicates

If your goal is Scenario B-combining cells in the same row while ensuring duplicate values within that row are not repeated-you can use a combination of TEXTJOIN, UNIQUE, and TRANSPOSE.

The Formula

For row 2 (combining A2 and B2), enter this formula in cell C2:

=TEXTJOIN(", ", TRUE, UNIQUE(TRANSPOSE(A2:B2)))

How It Works

1. TRANSPOSE(A2:B2): The UNIQUE function in Excel typically operates vertically on rows. Since our source cells are horizontal (across columns), we transpose them into a vertical array.
2. UNIQUE(...): This extracts the unique values from the transposed row data. If both A2 and B2 contain "Apple", it returns a single instance of "Apple". If they contain "Apple" and "Banana", it returns both.
3. TEXTJOIN(", ", TRUE, ...): This merges the unique values back together, separating them with a comma and a space. The second argument, TRUE, instructs Excel to ignore any empty cells.

Drag this formula down your column to apply it to all rows.

Method 3: The Power Query Method (For All Excel Versions & Large Datasets)

If you are working with large datasets, or if you are using an older version of Excel (like Excel 2016 or 2019) that does not support dynamic array functions, Power Query is the most robust alternative. It is highly efficient and easily repeatable.

Step-by-Step Guide:

1. Select your first data range, go to the Data tab, and click From Table/Range. This converts your range into an Excel Table and opens the Power Query Editor. Close and load it as a "Connection Only" query.
2. Repeat the first step for your second column/range so you have two separate queries loaded in the Power Query pane.
3. In the Power Query editor, go to the Home tab, click the dropdown next to Append Queries, and select Append Queries as New.
4. In the dialog box, select your two tables to append them vertically into a single query. Click OK.
5. Select the combined column in the Power Query editor, right-click the column header, and select Remove Duplicates.
6. Go to Home > Close & Load to output your perfectly deduplicated, combined column back into a new Excel worksheet.

Whenever your source data changes, simply go to the Data tab and click Refresh All to update your consolidated list instantly.

Method 4: Legacy Formulas (Excel 2019 and Older)

If you are stuck on an older version of Excel without UNIQUE or VSTACK, and you must use a standard formula rather than Power Query, you will need to rely on a complex array formula.

Because writing a single array formula to combine and deduplicate two independent ranges is highly complex and computationally expensive, the most stable legacy approach uses a helper column.

Step 1: Stack the Columns Manually or with a Simple Formula

First, place your data from both columns into a single column (for example, Column C). You can do this by copying and pasting, or by using a simple link formula in Column C:

=IF(ROW()<=COUNTA(A:A), INDEX(A:A, ROW()), INDEX(B:B, ROW()-COUNTA(A:A)))

Step 2: Extract Unique Values

Once your data is stacked in Column C, enter this classic CSE (Ctrl + Shift + Enter) array formula in cell D2 to extract unique values:

=IFERROR(INDEX($C$2:$C$20, MATCH(0, COUNTIF($D$1:D1, $C$2:$C$20), 0)), "")

Note: If you are using Excel 2019 or earlier, you must press Ctrl + Shift + Enter instead of just Enter to commit this formula. If done correctly, Excel will wrap the formula in curly braces { }.

Summary of Methods: Which One Should You Choose?

Conclusion

Combining columns while skipping duplicate values no longer requires tedious manual labor or complex VBA programming. If you are using the modern version of Microsoft 365, the =UNIQUE(VSTACK(...)) formula is your absolute best option, delivering speed and dynamic updates in a single line of code. For legacy systems or enterprise-grade data structures, Power Query offers a reliable, low-code alternative that keeps your workbook clean and organized.