How to sort data with a UDF function

[afndst]

´Hello
I am developing UDF funtions to compute the percentiles using the R methods.
For that purpose I need to sort the data within the function, sorting the data for another variable with no interaction with the spreadsheet.
The searching results I have been getting use the sort function of Excel to return the sorted data in a new range of the spreadsheet.
How can I achieve my intention?

Regards

António Teixeira









RTegard

 

[DanteAmor]

Hi @afndst. I hope you're well.

Check out these examples to sort arrays in memory, without using cell ranges.

Speed up search code currently in use

Hi, I am using the code below. On my userform in a TextBox i type a value to be found on my worksheet. All found values then populate my ListBox Sometimes a common value like FORD FIESTA is used. My issue is that the search / population can take some time & on occasion the userform vanishes...

www.mrexcel.com

Unique alphabetical list, omitting blank cells

My company does a "Daily Cash Sheet", to record tips between servers and bartenders or bussers. They list the names, in no particular order in cells G2:G15. I want to extract the names in alphabetical order, ignoring any blanks into a separate list. So, the following names in G2:G15: Jim...

www.mrexcel.com

 

[afndst]

Hello

It can be done using the worksheet SORT function with one column data sets. The code shown below transforms all the ranges submited into that format.

Function xlSort_wsf(x As Range, Optional ord = 1)

'ord = 1: Ascending
'ord = -1: Descending

Dim MyData() As Variant
Dim i As Long, n As Long
n = x.Cells.Count
ReDim MyData(1 To n, 1 To 1) ' Vector column

For i = 1 To n
MyData(i, 1) = x(i)
Next i

xlSort_wsf = WorksheetFunction.Sort(MyData, , ord)

End Function

RQuartis_018.xlsm
	A	B	C	D	E	F	G
1
2		1	20		1	-1
3		-1	10		-3	100
4		-3	20		-1	20
5		3	10		1	20
6		4	100		1	10
7		5	1		3	10
8					4	5
9		=xlSort_wsf($B$2:$C$7,E2)			5	4
10		=xlSort_wsf($B$2:$C$7,F2)			10	3
11					10	1
12					20	1
13					20	-1
14					100	-3
15
AUX01

Cell Formulas
Range	Formula
E3:F14	E3	=xlSort_wsf($B$2:$C$7,E2)
B9	B9	=FORMULATEXT(E3)
B10	B10	=FORMULATEXT(F3)
Dynamic array formulas.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 then you could use a formula

Fluff.xlsm
	A	B	C	D	E	F
1
2		1	20		1	-1
3		-1	10		-3	100
4		-3	20		-1	20
5		3	10		1	20
6		4	100		1	10
7		5	1		3	10
8					4	5
9					5	4
10					10	3
11					10	1
12					20	1
13					20	-1
14					100	-3
Main

Cell Formulas
Range	Formula
E3:E14	E3	=SORT(TOCOL(B2:C7),,E2)
F3:F14	F3	=SORT(TOCOL(B2:C7),,F2)
Dynamic array formulas.

 

[Rick Rothstein]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 then you could use a formula

Fluff.xlsm
	A	B	C	D	E	F
1
2		1	20		1	-1
3		-1	10		-3	100
4		-3	20		-1	20
5		3	10		1	20
6		4	100		1	10
7		5	1		3	10
8					4	5
9					5	4
10					10	3
11					10	1
12					20	1
13					20	-1
14					100	-3
Main

Cell Formulas
Range	Formula
E3:E14	E3	=SORT(TOCOL(B2:C7),,E2)
F3:F14	F3	=SORT(TOCOL(B2:C7),,F2)
Dynamic array formulas.

I was just in the process of typing this along with a UDF if the OP for some reason still wanted that. You posted the formula, so no need for me to do that, but here is the UDF...

Function xlSort(Rng As Range, Optional Ord As Long = 1) As Variant
  xlSort = Evaluate("SORT(TOCOL(" & Rng.Address & "),," & Ord & ")")
End Function

 

[afndst]

I was just in the process of typing this along with a UDF if the OP for some reason still wanted that. You posted the formula, so no need for me to do that, but here is the UDF...

Function xlSort(Rng As Range, Optional Ord As Long = 1) As Variant
  xlSort = Evaluate("SORT(TOCOL(" & Rng.Address & "),," & Ord & ")")
End Function

Hello

It's perfect.

Thanks