Function Problems

How to Convert Duplicate Excel Rows into Columns by Customer Reference

Rana Garcia Oct 13, 2026 871 views

Question details

The user needs a method to consolidate data where customer references are duplicated across multiple rows, transforming it so that each unique customer has a single row and their associated data points (e.g., countries) are distributed into separate columns.

How to Convert Duplicate Excel Rows into Columns by Customer Reference

Product: Spreadsheets
Device & OS: not provided
Scenario: Reorganizing a dataset to pivot repeated row attributes (like country names) into individual columns corresponding to a single unique customer ID.
Observed behavior: The current dataset lists the same customer reference multiple times across different rows for each associated attribute, rather than having a single row per customer.

Before you start

Ensure that your spreadsheet software is updated to a version that supports dynamic array functions (like UNIQUE, SORT, and FILTER) before applying these modern formula solutions.

Solution 1Recommended

Use Dynamic Array Formulas (UNIQUE, FILTER, TRANSPOSE)

This is the most direct and efficient method for extracting unique records and shifting the corresponding row data into multiple columns.

By combining the UNIQUE, FILTER, and TRANSPOSE functions, you can automatically extract distinct customer references and align all their corresponding values into adjacent columns. This method instantly updates if your source data changes.

Extract unique customer references

Select a blank cell where you want your new table to start (e.g., E1). Type the formula =SORT(UNIQUE(A1:A6)) and press Enter. This generates a sorted list of unique customer IDs.

Filter and transpose associated data

In the cell immediately to the right of your first unique ID (e.g., F1), enter the formula =TRANSPOSE(SORT(FILTER($B$1:$B$6,$A$1:$A$6=E1))).

Apply formula to remaining rows

Select cell F1, click and hold the fill handle (the small square at the bottom-right of the cell), and drag it down to apply the formula to the rest of the unique customer references.

Use Dynamic Array Formulas (UNIQUE, FILTER, TRANSPOSE)

Dynamic Updates: Because these are dynamic arrays, any new data added within the referenced ranges (A1:A6 and B1:B6) will automatically be sorted and transposed into your new layout.

Solution 2

Use a Single-Cell Advanced Formula (LET, REDUCE, LAMBDA)

Ideal for advanced users who want to generate the entire transformed table from a single cell without dragging formulas manually.

Solution 3

Group and Pivot Data Using Power Query

Best for extremely large datasets or complex data cleanup tasks where you prefer a visual interface over writing long formulas.

Easily Manage Complex Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE, FILTER, and TRANSPOSE, making it incredibly easy to transform duplicate rows into clean, multi-column tables.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the duplicate customer rows.
2. Extract the unique IDs: In a blank column, use the =UNIQUE() function to instantly generate a clean list of individual customers.
3. Transpose the data: Use the =TRANSPOSE(FILTER()) formula next to your unique IDs to flip the associated row values into separate columns.
4. Drag to complete: Pull the fill handle down to apply the logic to all customers and finalize your new table structure.

100% compatible with Microsoft Excel file formats (.xlsx) and modern array formulas.Lightweight architecture ensures fast calculations even with massive datasets.Familiar user interface makes transitioning from other spreadsheet software seamless.Free to use for daily data organization and analysis tasks.

microsoft office alternative - wps office

Frequently Asked Questions

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

These errors typically occur if your spreadsheet software version does not support modern dynamic array functions. Ensure you are using the latest version of WPS Office or Microsoft 365, which have built-in support for UNIQUE, FILTER, and arrays.

Can I achieve this using a standard Pivot Table?

Standard Pivot Tables are excellent for aggregating numerical data (like sums or averages) but cannot easily display text values (like country names) in the values area without using advanced Data Models and DAX measures. For text, the formula or Power Query methods are recommended.

How do I hide #N/A errors if some customers have fewer entries than others?

You can wrap your core formula in the IFERROR or IFNA function. For example, change your formula to =IFERROR(TRANSPOSE(FILTER(range, condition)), ""). This will replace any missing data errors with clean, blank cells.

Will my transposed columns update if I add new duplicate rows to the original data?

Yes. If you convert your original dataset into an official Table or use full column references (like A:A instead of A1:A6), the dynamic array formulas will automatically detect new entries, extract the unique names, and transpose the new data instantly.

Excel & Data Problems