Function Problems

How to Prevent Duplicate Results with UNIQUE and TOROW in Excel

John Wilson Sep 25, 2026 869 views

Question details

The user wants to stop an Excel nested formula from returning duplicate values when converting an array into a row.

Product: Excel
Device & OS: not provided
Scenario: Using a combined formula of UNIQUE, FILTER, and TOROW to extract unique values from a dataset and display them horizontally.
Observed behavior: The formula returns duplicate values instead of unique ones because the UNIQUE function was mistakenly configured with the 'TRUE, TRUE' arguments.

Before you start

Ensure your version of spreadsheet software supports Dynamic Array formulas, as older versions may not evaluate UNIQUE, FILTER, or TOROW correctly.

Solution 1Recommended

Adjust the UNIQUE Function Arguments to FALSE

Modify the 'by_col' and 'exactly_once' arguments in the UNIQUE function to ensure it properly filters unique items by row instead of looking for unique columns.

The UNIQUE function takes three arguments: array, by_col, and exactly_once. Passing TRUE to the second argument tells Excel to compare columns instead of rows. Passing TRUE to the third argument forces it to only return items that appear exactly one time in the source data. Changing these to FALSE (or omitting them) resolves the duplication issue.

Enter Editing Mode

Double-click the cell containing your formula, or click it once and place your cursor in the Formula Bar at the top of the window.

Locate the UNIQUE Arguments

Find the section of your formula containing the UNIQUE function. It likely looks similar to UNIQUE(FILTER(...), TRUE, TRUE).

Remove or Change to FALSE

Delete the TRUE, TRUE arguments so it defaults to row-based deduplication, or manually type FALSE, FALSE. Your corrected formula segment should look like UNIQUE(FILTER(range, criteria, ""), FALSE, FALSE).

Apply the Formula

Press the Enter key. Your complete working formula should resemble =TOROW(UNIQUE(FILTER($W$29:$W$94,$V$29:$V$94=A2, "")), 3, FALSE).

Adjust the UNIQUE Function Arguments to FALSE

Argument Defaults: If you omit the second and third arguments entirely, Excel defaults them to FALSE, which automatically checks for unique rows and returns all distinct items.

Solution 2

Use TRANSPOSE as a Simpler Alternative

Use the TRANSPOSE function instead of TOROW to flip your vertical array horizontally without complex argument configurations.

Advanced Data Handling

Easily Extract Unique Values Using WPS Spreadsheet

WPS Spreadsheet fully supports Dynamic Array functions like UNIQUE, FILTER, and TRANSPOSE, making it simple to extract and reorganize complex data without duplicate values.

1. Open Your Workbook: Launch WPS Spreadsheet and open the document containing your dataset.
2. Write the Base Formula: In an empty cell, type =UNIQUE(FILTER(range, criteria)) to instantly extract your target unique data.
3. Wrap in TOROW or TRANSPOSE: Modify the formula by wrapping it: =TOROW(UNIQUE(FILTER(range, criteria)), 3) to lay out the results horizontally while ignoring blanks.
4. Press Enter: Hit Enter to dynamically populate the unique values across the row.

100% compatible with Microsoft Excel file formats (.xlsx, .xls) and complex formulas.Built-in support for advanced dynamic arrays like UNIQUE, FILTER, and TOROW.Lightweight, completely free to use, and features a familiar tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my UNIQUE function return duplicate values?

The UNIQUE function looks for exact matches. If your source data has trailing spaces, different data types (like numbers stored as text), or if the 'by_col' argument is incorrectly set to TRUE when checking row data, the function will treat visually identical items as distinct and return duplicates.

What does the 3 mean in the TOROW function?

The second argument in the TOROW function controls what data types the formula should ignore. Setting it to 3 tells the function to ignore both blank cells and errors when flattening the array into a single row.

Are UNIQUE and TOROW available in all versions of Excel?

No. UNIQUE, FILTER, and TOROW are Dynamic Array functions. They are only available in newer versions of spreadsheet software, such as Microsoft 365, Excel 2021, and the latest versions of WPS Office.

Excel & Data Problems