Function Problems

How to Sort Unique Excel Table Values by Another Column

John Wilson Sep 25, 2026 875 views

Question details

The user needs a formula to extract unique descriptions from an Excel table and sort them by their associated nominal codes.

How to Sort Unique Excel Table Values by Another Column

Product: Excel
Device & OS: not provided
Scenario: Organizing table data by extracting a deduplicated list of items based on another column's sorting order.
Observed behavior: Attempting to use UNIQUE before SORTBY causes array length mismatches, requiring the data to be sorted before deduplication.

Before you start

Ensure your version of the spreadsheet application supports dynamic array formulas, such as SORTBY, UNIQUE, and CHOOSECOLS.

Solution 1Recommended

Use Dynamic Array Formulas (SORTBY, FILTER, UNIQUE)

Combine SORTBY and UNIQUE functions, ensuring you sort the full array before applying UNIQUE to prevent mismatched array length errors.

When sorting an array based on another column, the lengths of both arrays must be identical. If you apply the UNIQUE function to your target column first, it will shrink the array, causing a mismatch with the sorting column and returning an error. To resolve this, sort the entire dataset first, then extract the column and apply UNIQUE.

Identify Target Columns

Locate the description column you want to extract (e.g., 'NomDesc') and the column you want to sort by (e.g., 'Nominal').

Filter the Data (Optional but common)

If you only need a specific subset of data, use the FILTER function to isolate the relevant rows for both columns. For example: FILTER(Table[[NomDesc]:[Nominal]], Table[Heading]="Overheads")

Apply SORTBY

Use the SORTBY function to sort the filtered data array using your secondary column (Nominal code) as the sort key.

Extract and Deduplicate

Wrap the sorted result in CHOOSECOLS to extract just the target description column (column 1 in your array), and finally wrap it all in UNIQUE to remove duplicates.

Final Formula Example

Enter a formula similar to this into your target cell: =UNIQUE(CHOOSECOLS(SORTBY(FILTER(TBQ[[NomDesc]:[Nominal]],TBQ[Heading]="Overheads"),FILTER(TBQ[Nominal],TBQ[Heading]="Overheads")),1))

Use Dynamic Array Formulas (SORTBY, FILTER, UNIQUE)

Dynamic Spilling: Press Enter, and the resulting deduplicated, sorted list will automatically spill down the column.

Solution 2

Use a Pivot Table for Sorting Unique Values

If you are using an older version that does not support dynamic array functions, you can use a Pivot Table to group and sort unique items without formulas.

Efficient Data Analysis with WPS Spreadsheet

Easily Sort and Filter Unique Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE, FILTER, and SORTBY, enabling you to extract and organize your data effortlessly without encountering array length mismatches.

1. Open Your Data: Launch WPS Spreadsheet and open the document containing your data table.
2. Select Output Cell: Click on the empty cell where you want the sorted unique list to begin.
3. Input Dynamic Formula: Type in your combined UNIQUE, CHOOSECOLS, and SORTBY formula.
4. Spill the Results: Press Enter to instantly generate a deduplicated, correctly sorted list that spills automatically into adjacent rows.

Native support for advanced dynamic array functions like SORTBY, UNIQUE, and CHOOSECOLS.Fully compatible with Microsoft Excel file formats (.xlsx) for seamless transitions.Lightweight, fast, and completely free to use.Intuitive interface making complex data analysis simple.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a #VALUE! error when using SORTBY with UNIQUE?

This error usually occurs if the arrays being sorted do not have matching lengths. If you apply UNIQUE to one column before sorting, its length shrinks, causing a mismatch with the unaltered sort-by column. Always sort the full data first, extract the column, and then apply UNIQUE.

Does the CHOOSECOLS function work in older versions of Excel?

No, CHOOSECOLS is a modern dynamic array function. If you are using an older version, consider using alternative methods like INDEX/MATCH combinations or Pivot Tables to organize your data.

Can I sort the unique list in descending order?

Yes. In the SORTBY function, you can specify the sort order by adding '-1' as the third argument (sort_order). This will sort your specified column in descending order before you apply the UNIQUE function.

Excel & Data Problems