how to sort small to big horizontally

[excelNewbie22]

hi, i'm using this function with the udf below,
the problem is that it doesnt sorting the number small to big,
can you help me please?

test
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	1	2	3	4	5	6
2	1	2	3	4	5	36			1	11	12	15	16	2	22	23	3	36	37	38	4	40	5	6	7	8	9
3	2	3	11	15	16	37
4	3	8	9	12	15	38
5	6	7	8	22	23	40
test

Cell Formulas
Range	Formula
K2:AC2	K2	=INDEX(SORT(UNIQUES(C2:H5)),SEQUENCE(,COUNTA(UNIQUES(C2:H5))))
K5	K5	=INDEX(SORT(UNIQUES(C6:H9)),SEQUENCE(,COUNTA(UNIQUES(C6:H9))))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:H5	Cell Value	duplicates	text	NO
I1:I5	Cell Value	>4	text	NO
I1:J5	Cell Value	=6	text	NO

Function UNIQUES(rng As Range) As Variant()
Dim list As New Collection
Dim Ulist() As Variant

'Adding each value of rng to the collection.
On Error Resume Next
For Each Value In rng
    'here value and key are the same. The collection does not allow duplicate keys hence only unique values will remain.
    list.Add CStr(Value), CStr(Value)
Next
On Error GoTo 0

'Defining the length of the array to the number of unique values. Since the array starts from 0, we subtract 1.
ReDim Ulist(list.Count - 1, 0)

'Adding unique value to the array.
For i = 0 To list.Count - 1
    Ulist(i, 0) = list(i + 1)
Next

'Printing the array
UNIQUES = Ulist
End Function

 

[lrobbo314]

Does this do what you are looking for?

COMPSTAT Template.xltx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	1	2	3	4	5	6
2	1	2	3	4	5	36		1	2	3	4	5	6	7	8	9	11	12	15	16	22	23	36	37	38	40
3	2	3	11	15	16	37
4	3	8	9	12	15	38
5	6	7	8	22	23	40
Data

Cell Formulas
Range	Formula
H2:Z2	H2	=LET(t,A2:F5,r,ROWS(t),s,SEQUENCE(r*COLUMNS(t),,0),rs,MOD(s,r)+1,cs,INT(s/r)+1,i,INDEX(t,rs,cs),TRANSPOSE(UNIQUE(SORT(i))))
Dynamic array formulas.

 

[excelNewbie22]

brilliant!
and with no udf, even better!
thank you lrobbo314

 

[lrobbo314]

Just for fun, here's how I would do it just using the UDF.

COMPSTAT Template.xltx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	1	2	3	4	5	6
2	1	2	3	4	5	36		1	2	3	4	5	6	7	8	9	11	12	15	16	22	23	36	37	38	40
3	2	3	11	15	16	37
4	3	8	9	12	15	38
5	6	7	8	22	23	40
Data

Cell Formulas
Range	Formula
H2:Z2	H2	=hus(A2:F5)
Dynamic array formulas.

Function HUS(r As Range)
Dim AR() As Variant:        AR = r.Value2

With CreateObject("System.Collections.ArrayList")
    For Each itm In AR
        If Not .Contains(itm) Then .Add (itm)
    Next itm
    .Sort
    HUS = .toArray
End With
End Function

 

[excelNewbie22]