excelsortinguniquevstack

Excel SORT Function in Conjunction With UNIQUE, TOROW & VSTACK

I need to extract unique values from multiple ranges (only two ranges in my example, but more will be added) and display them in a single row, sorted in ascending order, without duplicates.

So far, I have used the following formula:

=SORT(UNIQUE(TOROW(VSTACK(B36:AF36, B67:AF67),1)),,1)

However, this formula is not sorting the values correctly and not removing duplicates as expected.

Example:

Given Data:

B36:AF36 → {1, 2, 3, 8, 15}

B67:AF67 → {4, 6, 15, 16}

Current Output (Incorrect)

{1, 2, 3, 8, 15, 4, 6, 15, 16} (Concatenated but not sorted, and 15 appears twice)

Expected Output (Correct)

{1, 2, 3, 4, 6, 8, 15, 16} (Sorted in ascending order with duplicates removed)

What formula should I use to ensure that the values from all ranges are correctly combined, sorted, and displayed uniquely in a single row?

Thanks in advance for any help!

Solution

  • You were almost there:

    =SORT(UNIQUE(HSTACK(A1:E1,A2:E2),TRUE),,,TRUE)

    Make sure the functions you are using work in the same direction - horizontal or vertical:

    enter image description here

    1. Stack your arrays horizontally
    2. Take unique values, make sure to use 'TRUE' to return unique columns
    3. Sort provided array by columns.

    Edit: if empty cells have to be filtered out:

    =LET(
input, HSTACK(B36:AF36,B67:AF67),
SORT(UNIQUE(FILTER(input,HSTACK(input)<>"",""),TRUE),,,TRUE))