CODEWITHSUNDEEP
excelsortinguniquevstack
cj69
cj69

Reputation: 113

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!

Upvotes: 3

Views: 68

Answers (2)

Michal
Michal

Reputation: 6064

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

Upvotes: 2

Harun24hr
Harun24hr

Reputation: 37050

You may try the following formula-

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

Input data:
1 2 3 8 15
4 6 15 16

Output:
1 2 3 4 6 8 15 16

enter image description here

Upvotes: 0

Related Questions