Function Problems

How to Concatenate Unique Values from Multiple Columns in Excel

Bushra Parveen Oct 1, 2026 869 views

Question details

The user wants to extract distinct values from a specific horizontal range of columns (AV through BC) in the same row and combine those values into a single cell using a fillable formula.

How to Concatenate Unique Values from Multiple Columns

Product: Spreadsheets
Device & OS: not provided
Scenario: Consolidating horizontal row data into a single cell while ensuring duplicate entries are ignored.
Observed behavior: Requires a dynamic array formula that can output unique, concatenated text values and be successfully dragged down to apply to subsequent rows.

Before you start

Ensure you are using a modern version of your spreadsheet software (such as Microsoft 365 or the latest WPS Office) that supports dynamic array functions. Also, verify that your target data range does not contain merged cells, as they can interfere with array calculations.

Solution 1Recommended

Use Dynamic Array Functions (ARRAYTOTEXT, UNIQUE, TOROW)

This is the most efficient and recommended method to extract distinct values across columns and join them into a text string automatically.

By combining modern dynamic array functions, you can bypass complex VBA macros. This approach automatically ignores empty cells, isolates unique items horizontally, and formats them into a single comma-separated string.

Select the target cell

Click on the cell where you want the concatenated result to appear. In this scenario, click on cell AU2.

Input the array formula

Type the following formula into the formula bar: =ARRAYTOTEXT(UNIQUE(TOROW(AV2:BC2,1),TRUE)).

Apply and fill down

Press the Enter key to execute the formula. Then, click the small square at the bottom-right corner of cell AU2 and drag it down to apply the formula to the remaining rows in your dataset.

Use Dynamic Array Functions (ARRAYTOTEXT, UNIQUE, TOROW)

How the Formula Works: The TOROW function with the '1' argument converts the row into an array while ignoring blanks. The UNIQUE function with the 'TRUE' argument extracts distinct values horizontally. Finally, ARRAYTOTEXT converts that array into a single comma-separated text string.

Solve Formula Issues with WPS Office

Easily Manage Advanced Formulas in WPS Spreadsheets

WPS Spreadsheets provides comprehensive support for modern array functions, allowing you to manipulate, filter, and concatenate complex data with ease. Enjoy a smooth and familiar interface designed to boost your productivity without steep learning curves.

1. Open your dataset in WPS Spreadsheets: Launch WPS Office and open your .xlsx file containing the data.
2. Navigate to the output column: Click on the first cell of your output column (e.g., AU2).
3. Enter the concatenation formula: Type =ARRAYTOTEXT(UNIQUE(TOROW(AV2:BC2,1),TRUE)) in the formula bar and press Enter.
4. Fill the formula down: Double-click the fill handle on the bottom right of the cell to automatically populate the formula down the entire column.

Fully compatible with Microsoft Excel formulas, functions, and .xlsx files.Native support for advanced dynamic arrays like UNIQUE, TOROW, and ARRAYTOTEXT.Free, lightweight, and incredibly fast even with large datasets.Familiar tabbed user interface for seamless workflow migration.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the formula returning a #NAME? error?

A #NAME? error typically indicates that your spreadsheet software version does not support newer dynamic array functions like TOROW or ARRAYTOTEXT. You will need to update to the latest version of Microsoft 365 or WPS Office.

Can I use a different delimiter instead of a comma?

Yes. ARRAYTOTEXT uses a comma and a space by default. To use a custom delimiter like a hyphen or semicolon, replace ARRAYTOTEXT with the TEXTJOIN function. For example: =TEXTJOIN(" - ", TRUE, UNIQUE(TOROW(AV2:BC2, 1), TRUE)).

How can I sort the unique values alphabetically before concatenating them?

You can nest the SORT function inside your formula. Update your formula to include SORT right before the UNIQUE function. The formula will look like this: =ARRAYTOTEXT(SORT(UNIQUE(TOROW(AV2:BC2,1),TRUE),1,1,TRUE)).

Excel & Data Problems