Function Problems

How to Combine Duplicate Excel Values and List Unique Related Values

Camila Milosovich Oct 1, 2026 868 views

Question details

The user wants to extract unique values from a column containing duplicates and list their corresponding values from another column in adjacent horizontal cells.

How to Combine Duplicate Excel Values and List Unique Related Values in Excel

Product: Excel
Device & OS: not provided
Scenario: Organizing and summarizing a dataset where multiple related entries exist for a single duplicated identifier, making it easier to read as a matrix or cross-tabulation.
Observed behavior: The data is currently listed in two vertical columns with repeating values in the first column. The goal is to restructure it so each unique identifier appears only once, with its associated values spread horizontally across adjacent columns.

Before you start

Ensure you are using a modern spreadsheet application that supports dynamic array functions, such as Excel 365, Excel 2021, or the latest version of WPS Office.

Solution 1Recommended

Use UNIQUE, FILTER, and TRANSPOSE Functions

This method uses a combination of modern dynamic array formulas to automatically extract unique values and spread their related data horizontally.

By combining the SORT and UNIQUE functions, you can create a clean, alphabetized list of your identifiers. Then, the FILTER function paired with TRANSPOSE extracts the related items and flips them from a vertical layout to a horizontal one.

Extract the unique identifiers

Select a blank cell (e.g., D2) where you want the unique list to start. Enter the formula =SORT(UNIQUE(A2:A14)) to extract and alphabetize the unique values from Column A.

Filter and transpose the related values

In the adjacent cell (e.g., E2), enter the formula =TRANSPOSE(FILTER($B$2:$B$14,$A$2:$A$14=D2)) to filter the related values from Column B that match the identifier in D2, and display them horizontally.

Apply to the remaining rows

Press Enter to apply the formula. Then, click and drag the fill handle (the small square at the bottom-right of cell E2) down to apply this formula to the rest of the unique values in your new list.

Use UNIQUE, FILTER, and TRANSPOSE Functions

Version Requirement: Functions like UNIQUE, FILTER, and TRANSPOSE are dynamic array functions and require Microsoft Excel 365, Excel 2021, or compatible modern software like WPS Office.

Solution 2

Use the LET Function for an All-in-One Array Result

Advanced users can utilize a single, complex LET formula to spill the entire customized matrix at once without needing to drag formulas down.

Powerful Spreadsheet Tool

Easily Combine Duplicate Values and Filter Data in WPS Office

WPS Office Spreadsheets fully supports modern dynamic array formulas like UNIQUE, FILTER, and TRANSPOSE, making it incredibly easy to reorganize duplicate entries and group associated values.

1. Open your spreadsheet: Launch WPS Office and open your dataset containing the duplicate values.
2. Apply dynamic arrays: Use the =UNIQUE() function to extract distinct identifiers, and combine with =TRANSPOSE(FILTER()) to instantly list matching values.
3. Save seamlessly: Save your newly organized file directly as an .xlsx document to maintain perfect compatibility.

Fully compatible with Microsoft Excel formulas and .xlsx formats.Natively supports advanced dynamic arrays including UNIQUE and FILTER.Lightweight, fast, and completely free for standard data tasks.Familiar tabbed interface with zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #NAME? error when using these formulas?

The #NAME? error usually occurs if your spreadsheet software does not support modern dynamic array functions. You need to use Excel 365, Excel 2021, or the latest version of WPS Office to access functions like UNIQUE and FILTER.

How can I combine related values into a single cell instead of separate columns?

If you want the related values grouped together in one cell (for example, separated by commas), you can use the TEXTJOIN function instead of TRANSPOSE. The formula would look like this: =TEXTJOIN(", ", TRUE, FILTER($B$2:$B$14, $A$2:$A$14=D2)).

Can I list the unique related values vertically instead of horizontally?

Yes. To list the extracted values vertically, simply remove the TRANSPOSE function from the formula. Using =FILTER($B$2:$B$14, $A$2:$A$14=D2) will spill the related data vertically in the rows beneath the formula.

Excel & Data Problems