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!

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!

• excel
• sorting
• unique
• vstack

2 Answers 2

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:

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))

You may try the following formula-

=TOROW(SORT(UNIQUE(TOCOL(A1:E2,1))))

Input data:

Output: