How to Combine Duplicate Contact Names and Keep Multiple Email Addresses in Excel and WPS Spreadsheets
Question details
The user needs to organize a spreadsheet contact list so each person appears once while all associated email addresses remain available in separate columns.
- Product
- WPS Office Spreadsheets / Microsoft Excel
- Device & OS
- not provided
- Scenario
- Organizing a contact list for a cleaner, deduplicated view while preserving all email data.
- Observed behavior
- The current contact list has repeated name rows with one email per row; the desired result is one row per person and the matching email addresses spread across adjacent columns.
Open the contact list in WPS Spreadsheets and confirm whether your version supports dynamic array functions such as UNIQUE and FILTER; if not, plan to use the INDEX and SMALL array formula. Before combining, make sure each equivalent contact name is spelled exactly the same, because formulas treat "John Smith" and "John Q. Smith" as different names.
Use UNIQUE, SORT, FILTER, and TRANSPOSE formulas to consolidate duplicates
Best when you use current WPS Spreadsheets, Microsoft 365, or Excel 2021 and newer; it automatically updates when names or emails change.
Dynamic array formulas spill results into adjacent cells and remove the need to drag formulas down. They treat names as distinct only when spelled identically, so standardize names first.
Double-click the .xlsx file to open it in WPS Office Spreadsheets, then confirm which columns contain names and email addresses.
Select an empty cell where the unique list should start, type =SORT(UNIQUE(A2:A2000)), and press Enter. WPS Spreadsheets spills the sorted unique names down the column; adjust A2:A2000 to match your actual name range.
In the cell next to the first unique name, type =TRANSPOSE(FILTER($B$2:$B$2000,$A$2:$A$2000=E2,"")) and press Enter. If your data is in an Excel table, use =TRANSPOSE(FILTER(Table1[Emails],Table1[Names]=E2)).
Drag the fill handle from the first formula cell down to the last unique name, or double-click the fill handle to fill the list. Each row now shows all matching email addresses in separate columns.
Use INDEX and SMALL array formula in older Excel and WPS versions
Use this method when your spreadsheet version does not support dynamic arrays, such as Excel 2013 or an older WPS Spreadsheets formula engine.
Consolidate duplicate contacts in WPS Office Spreadsheets
Open the Excel contact list in WPS Office Spreadsheets, use the UNIQUE, SORT, FILTER, and TRANSPOSE formulas to keep one row per person, and save the file as .xlsx to maintain Microsoft Excel compatibility.
- 1. Open WPS Office Spreadsheets: Download WPS Office from the official website, install it, and launch WPS Spreadsheets on your computer.
- 2. Open the contact list file: Click Menu > Open, select the Excel .xlsx contact list, and verify the columns that contain names and emails.
- 3. Create the unique name list: Select an empty cell and enter =SORT(UNIQUE(A2:A2000)), then press Enter. Adjust the range to match your actual name column.
- 4. Extract matching email addresses: Next to the first unique name, enter =TRANSPOSE(FILTER($B$2:$B$2000,$A$2:$A$2000=E2,"")) and press Enter. Drag the formula down through the unique name list.
- 5. Save the consolidated contact list: Click Menu > Save As, choose Excel Workbook (.xlsx), and save the file so it remains compatible with Microsoft Excel.
Frequently Asked Questions
Do these formulas work in WPS Office if the contact list was created in Microsoft Excel?
Yes. WPS Office Spreadsheets opens .xlsx files and supports the UNIQUE, SORT, FILTER, and TRANSPOSE formulas in current versions. If your WPS version uses the legacy calculation engine, use the INDEX and SMALL array formula and press Ctrl+Shift+Enter.
Why does my formula return different rows for the same person?
Excel and WPS Spreadsheets treat text as case-insensitive but not spelling-insensitive. "John Smith", "John Q. Smith", and "John Smith " are considered different. Use Find & Replace or the TRIM function to standardize name spelling and remove leading or trailing spaces before combining.
Can I keep more than three email addresses for one contact?
Yes. The TRANSPOSE(FILTER(...)) formula spills all matching emails into adjacent columns, so you are not limited to a fixed number. Just make sure the cells to the right are empty so the spilled results can fill them.
What should I do if I have an older Excel version without UNIQUE and FILTER?
Use the INDEX and SMALL array formula. Enter the formula in E2, press Ctrl+Shift+Enter if your version requires it, then fill it across to enough columns and down through all unique names. Adjust the A and B column ranges to your data.
How do I remove duplicate names without losing email data in WPS Spreadsheets?
Do not use Remove Duplicates on the original name and email ranges before extracting the email addresses, because Remove Duplicates keeps only one row per duplicate. Instead, keep the original email rows, create a separate unique name list with UNIQUE and SORT, and use FILTER and TRANSPOSE to pull all matching emails into separate columns.
