Data Import & Export

How to Combine and Deduplicate Address Lists in Excel

Nimra Malik Sep 27, 2026 870 views

Question details

The user needs to combine two separate address and neighborhood lists into a single list, remove any duplicate entries, and sort the final result.

How to Combine and Deduplicate Address Lists in Excel

Product: Excel
Device & OS: not provided
Scenario: Consolidating manually entered address data from multiple ranges into a clean, unified, and organized directory.
Observed behavior: The manually entered lists currently exist in separate ranges and contain overlapping records and inconsistent formatting (such as 'Street' versus 'St'), requiring consolidation and deduplication.

Before you start

Before merging your lists, it is highly recommended to standardize common abbreviations (like using Find and Replace to change all instances of 'St' to 'Street') and remove trailing spaces to ensure the deduplication process works accurately.

Solution 1Recommended

Use VSTACK, UNIQUE, and SORT Formulas

This method uses modern dynamic array functions to stack the data, remove duplicates, and sort the combined results all in a single step.

Dynamic array formulas allow you to manipulate multiple ranges of data without altering the original source. Combining VSTACK to merge lists, UNIQUE to remove exact matches, and SORT to alphabetize makes this process highly efficient.

Select the destination cell

Click on a blank cell where you want the top-left corner of your new combined address list to begin.

Enter the combination formula

Type the formula =SORT(UNIQUE(VSTACK(A2:B15,E2:F22))), replacing 'A2:B15' and 'E2:F22' with the actual ranges of your two address lists.

Generate the array

Press Enter. Excel will automatically spill the results, generating a stacked, deduplicated, and alphabetically sorted list.

Clean up and paste as values

Review the results. If inconsistent text caused missed duplicates, manually clean those matching entries. Finally, copy the entire spilled array and use 'Paste as Values' to convert the dynamic formula into static text.

Version Compatibility: The VSTACK function is available in Microsoft 365, Excel 2024, and the latest versions of WPS Office. If you are using an older version, you will need to manually copy and paste the lists together before deduplicating.

Solution 2

Manually Stack and Use the Remove Duplicates Tool

If your spreadsheet software does not support the VSTACK formula, you can manually consolidate your data and use the built-in deduplication tool.

Efficient Data Management with WPS

Combine and Clean Address Lists Effortlessly in WPS Spreadsheet

WPS Spreadsheet offers powerful data management tools, including support for modern dynamic array formulas like VSTACK and UNIQUE, allowing you to seamlessly merge and clean your address lists for free.

1. Open your document: Launch WPS Spreadsheet and open the file containing your separate address lists.
2. Apply the dynamic formula: Select an empty cell and enter =SORT(UNIQUE(VSTACK(Range1, Range2))), highlighting your specific data ranges.
3. Finalize your list: Press Enter to instantly merge the lists. You can easily copy and 'Paste as Values' from the right-click menu to secure your final text.

Fully compatible with Microsoft Excel formulas, including advanced array functions.Built-in Data tools like Remove Duplicates and Sort for intuitive list management.Lightweight application that handles large datasets smoothly without lagging.Free, easy-to-use interface that requires zero learning curve for Excel users.

Frequently Asked Questions

Why did the formula leave some duplicate addresses in my list?

The UNIQUE formula is strictly case and text-sensitive. If one address uses 'Street' and the other uses 'St', or if there are invisible trailing spaces at the end of a word, the formula treats them as entirely different entries. Use the Find and Replace tool to standardize abbreviations before applying the formula.

Can I combine more than two address lists using VSTACK?

Yes, the VSTACK function supports multiple arrays. You can combine three or more lists by simply adding them separated by commas, for example: =SORT(UNIQUE(VSTACK(A2:B15, E2:F22, H2:I20))).

How do I convert the dynamic formula results into normal editable text?

Select the entire block of cells generated by your formula, press Ctrl+C to copy, then right-click on the same selection (or a new destination cell) and choose 'Paste as Values' (usually represented by an icon with a clipboard and the number 123). This removes the underlying formula.

Excel & Data Problems