Order an array from the smallest number to the biggest

[imakaveli]

Hello guys, as the title says, I am looking for a code that, in an array of numbers, ordinate it in another array from the smallest number to the biggest, do you have any suggestions?

Thank you so much for the help you would like to give me
Cheers from Milan
iMak

[Bob Phillips]

Drop it into a range and sort.

[imakaveli]

Drop it into a range and sort.

Oops, I think I did not explain the problem in a clear way! I would like to do all this in VBA creating 2 virtual arrays, I mean, I have this virtual array not ordered and i want to create a second one ordered from the smallest value to the biggest, so i can choose at the end the smallest value and put it in my Excel paper!
The first post seems that I want to to this in Excel and not with VBA
Thank you for your reply xld

Cheers
iMak

[mdmackillop]

Is this a single or multiple column array?

[Bob Phillips]

I still suggest the same, just adding ou then load the sorted range into a new array.

[imakaveli]

Is this a single or multiple column array?

I want to order a single column array, thank you for answering

I still suggest the same, just adding ou then load the sorted range into a new array.

The point is that I am trying to build an index and I have to do this operation for hundreds of cells each and for each cell I want to build a virtual array, order it, pick the lowest number and put it in the cell, I think that your operation is great if you have to do this operation only once, or at least is what I have understood (maybe I didn't understand, sorry, I am not english mothertongue )
By the way, is there a VBA code I can use to pick the lowest number of the array or I have to order it first?

Thank you very much to everyone for your help
iMak

[Bob Phillips]

Are you saying that the cells have an array in them, each and every cell?

Smallest number

[vba]

minVal = Application.Min(ary)
[/vba]

[imakaveli]

Are you saying that the cells have an array in them, each and every cell?

Smallest number

[vba]

minVal = Application.Min(ary)
[/vba]

Nope, I didn't explain it clearly, I am analizing a database of deals, for each deal I have to create an array of the previous deals with their year of entry, and pick the smallest year an the analize the next deal; so basically for each row I have to build an array of the previous deals, pick the smallest number, put it in the result and go to the next deal!
I hope to have explained it in a better way!

And about the minVal=Application.Min(ary), I don't understant where you have to put the range you want to analize, is ary the place where I have to put it?

Thank you very much for your help

EDIT: I need to order it also because not only I have to pick the smallest, but also the second smallest, the third and so on, so I think that using only the formula to pick the minumum value is not enough...

[Bob Phillips]

And about the minVal=Application.Min(ary), I don't understant where you have to put the range you want to analize, is ary the place where I have to put it?

You said you wanted the minimum value in an array, so as I gave ...

But it is the same for a range as an array.

EDIT: I need to order it also because not only I have to pick the smallest, but also the second smallest, the third and so on, so I think that using only the formula to pick the minumum value is not enough...

[vba]

someVal = Application.Small(ary, 1)
someVal2 = Application.Small(ary, 2)
etc.
[/vba]

[imakaveli]

You said you wanted the minimum value in an array, so as I gave ...

But it is the same for a range as an array.



[vba]

someVal = Application.Small(ary, 1)
someVal2 = Application.Small(ary, 2)
etc.
[/vba]

Ah ok, so with the word "ary" you mean array, so in my code instead of "ary" I have to put the name of my array! Sorry if I didn't understand this
About the reordering of the array, I cannot do it manually in Excel because I must keep the order, is there a way I can order this virtual array in VBA without touching Excel?

[Bob Phillips]

Yes there is, but as MD says, how many dimensions does the arry have? I assume 2, year and amount?

[imakaveli]

Yes there is, but as MD says, how many dimensions does the arry have? I assume 2, year and amount?

It's only one dimension, because in the virtual array there gonna be this formula:
((year of the deal we are analizing - year of one of the previous deals)*12) + month of the deal we are analyzing - month of one of the previous deals

so the virtual array is only one column in which each cell contains this operation

[imakaveli]

You said you wanted the minimum value in an array, so as I gave ...

But it is the same for a range as an array.



[vba]

someVal = Application.Small(ary, 1)
someVal2 = Application.Small(ary, 2)
etc.
[/vba]

Excuse me again xld, I have tried to use the code
someVal = Application.Small(ary, 1)
someVal2 = Application.Small(ary, 2)
to pick the smallest and the second smallest but it doesn't work, while the Application.min to calculate the smallest value seems to work, what am I doing wrong?
I'm sorry f I keep on annoying you with these questions...
Thank you
iMak

EDIT: btw, I am using Excel 2002, maybe this formula runs only on the newer versions of VBA...

[Saladsamurai]

I have not worked out the logic of his code, but you might want to check about 3/4 down on this page.

[Bob Phillips]

What I gave you should work, on any Excel version. That it doesn't suggests that we are missing something. Could you possibly post your workbook, with the non-working code, so that we can look see?

[Paul_Hossler]

You can pass the range to SortedRange and either return the sorted data back to the Worksheet using an Array Formula, or do something like Test() and process it with in VBA

[VBA]
Option Explicit
Sub Test()
Dim v As Variant
v = SortedRange(Worksheets("Sheet1").Range("A1:A40"))
MsgBox "Smallest is " & v(LBound(v), 1)
MsgBox "Next Smallest is " & v(LBound(v, 1) + 1, 1)
MsgBox "Largest is " & v(UBound(v, 1), 1)

End Sub

Function SortedRange(r As Range) As Variant
Dim v() As Double
Dim x As Double
Dim i As Long, j As Long
Dim r1 As Range


Set r1 = Intersect(r, r.Parent.UsedRange).Columns(1)



ReDim v(1 To r1.Rows.Count, 1 To 1)

For i = 1 To r1.Rows.Count
v(i, 1) = r1.Cells(i, 1).Value
Next i

Call QuickSortColumn(v, LBound(v, 1), UBound(v, 1))

SortedRange = v
End Function
'call Quick_Sort (aryTemp(), LBound(aryTemp(), UBound(aryTemp())
Private Sub QuickSortColumn(ByRef SortArray As Variant, ByVal First As Long, ByVal Last As Long)
Dim Low As Long, High As Long
Dim Temp As Variant, List_Separator As Variant

Low = First
High = Last

List_Separator = SortArray((First + Last) \ 2, 1)

Do
Do While (SortArray(Low, 1) < List_Separator)
Low = Low + 1
Loop

Do While (SortArray(High, 1) > List_Separator)
High = High - 1
Loop

If (Low <= High) Then
Temp = SortArray(Low, 1)
SortArray(Low, 1) = SortArray(High, 1)
SortArray(High, 1) = Temp
Low = Low + 1
High = High - 1
End If

Loop While (Low <= High)

If (First < High) Then Call QuickSortColumn(SortArray, First, High)
If (Low < Last) Then Call QuickSortColumn(SortArray, Low, Last)
End Sub
[/VBA]

Paul

[imakaveli]

Basically my work is to calcolate a lot of indexes on some financial data. The database I am working on is only a little part of the big one I will work on later. The database is about 3 main funds (let's say A, B, C) which contain some deals inside them, so there are like 200 rows in my worksheet divided in 3 main groups, it's something like this:



For each financial deal (so for each row), which I will call the "focal deal" I want to calculate a first index about how many months passed after the previous deal with one same feature (in this case same sector), and I was able to do this with the Application.min code; now I am stuck with the second deal, which is about calculate the mean of the number of months from the last deal and the number of months from the "second last" deal.
To find the last deal I decided to calculate the deal that has the smallest number of months from the focal deal, that's why I calculated this formula:

formula 1: ((year of the focal deal - year of the previous deal)*12) + months of the focal deal - months of the previous deal

And at the end I want to do the mean of the months from the last and the months from the second last.
My problem that I was asking you at the beginning is that I have a virtual array of months calculated with the formula 1, and I want to pick the smallest and the second smallest, but while I was able to do this with Application.min, it's not working with Application. small and I don't know why... this is the code:

[VBA]

Public Function IndexProximityOfExperienceSectorLast2()

Dim vDeltaMesi As Variant

Dim nRow, q, r, t, x, z As Integer
Dim counter, mesi1, mesi2 As Variant 'counter lo tengo solo per riempire il vettore
Worksheets("Foglio1").Select
Range("C65536").Select
Selection.End(xlUp).Select
nRow = ActiveCell.Row
ReDim vDeltaMesi(2 To nRow, 1 To 1)

'This part is only to calculate the beginning and the end of the rows of the different funds
For q = 2 To 4
For r = Worksheets("foglio2").Cells(q, 2) To Worksheets("foglio2").Cells(q, 3)
t = Sheets("foglio2").Cells(q, 2) 'inizio fondo
x = Sheets("foglio2").Cells(q, 3) 'fine fondo
'aggiungere che al cambio di r il vettore si deve svuotare
counter = 0
ReDim vDeltaMesi(0 To 0)
ReDim vDeltaMesi(2 To nRow, 1 To 1)
'looking for the previous deals
For z = t To x
If z = r Then 'non si conta il FD
GoTo ESCI
End If
If Sheets("foglio1").Cells(r, 35) = Empty Then
GoTo ESCI
End If
'if the year is bigger then the one of the focal deal then next z
If Sheets("foglio1").Cells(z, 48) > Sheets("foglio1").Cells(r, 48) Then
GoTo ESCI
End If
' if the year if the same then I check the months
If Sheets("foglio1").Cells(z, 48) = Sheets("foglio1").Cells(r, 48) Then
' se il mese è -99 lo considero come 1
If Sheets("foglio1").Cells(r, 47) = "-99" Then
If Sheets("foglio1").Cells(z, 47) = -99 Or Sheets("foglio1").Cells(z, 47) = 1 Then
GoTo CONTINUA
Else: GoTo ESCI
End If
End If
'se il mese di z è maggiore del mese FD allora esco
If Sheets("foglio1").Cells(z, 47) > Sheets("foglio1").Cells(r, 47) Then
GoTo ESCI
End If
End If
CONTINUA:
'if sector of the previous deal is the same I fill a row of the virtual array vDeltaMesi (intendento -99 come gennaio)
'((annoFD-annoZ)*12)+mesiFD-mesiZ METTO ANNO DI ENTRY
If Sheets("foglio1").Cells(z, 35) = Sheets("foglio1").Cells(r, 35) Then
' w = w + 1 'counter lo tengo solo per riempire il vettore
'caso in cui anno entry FD sia > però l'anno di exit è minore (quindi z sarebbe successivo e quindi nn è esperienza)
If Sheets("foglio1").Cells(r, 51) - Sheets("foglio1").Cells(z, 51) < 0 Then
' Sheets("foglio1").Cells(r, 128) = "exitFD < exit deal in esame"
GoTo ESCI
End If
'caso in cui abbiano exit = ma il FB è -99 e il deal in esame nn lo è (verrebbe numero negativo)(per risolvere riga 60)
If Sheets("foglio1").Cells(r, 51) - Sheets("foglio1").Cells(z, 51) = 0 Then
If Sheets("foglio1").Cells(z, 51) > 1 Then
GoTo ESCI
End If
End If
If Sheets("foglio1").Cells(r, 50) = "-99" Then 'se r è -99 allose se z
If Sheets("foglio1").Cells(z, 50) = "-99" Then
vDeltaMesi(r, 1) = (Sheets("foglio1").Cells(r, 51) - Sheets("foglio1").Cells(z, 51)) * 12
GoTo ESCI
End If
vDeltaMesi(r, 1) = (Sheets("foglio1").Cells(r, 51) - Sheets("foglio1").Cells(z, 51)) * 12 + 1 - Sheets("foglio1").Cells(z, 50)
GoTo ESCI
End If
If Sheets("foglio1").Cells(z, 50) = "-99" Then 'se z è -99 allora se r
If Sheets("foglio1").Cells(r, 50) = "-99" Then
vDeltaMesi(r, 1) = (Sheets("foglio1").Cells(r, 51) - Sheets("foglio1").Cells(z, 51)) * 12
GoTo ESCI
End If
vDeltaMesi(r, 1) = (Sheets("foglio1").Cells(r, 51) - Sheets("foglio1").Cells(z, 51)) * 12 + Sheets("foglio1").Cells(r, 50) - 1
GoTo ESCI
End If
vDeltaMesi(r, 1) = (Sheets("foglio1").Cells(r, 51) - Sheets("foglio1").Cells(z, 51)) * 12 + Sheets("foglio1").Cells(r, 50) - Sheets("foglio1").Cells(z, 50)
End If
ESCI:
Next z
mesi1 = Application.Small(vDeltaMesi, 1)
mesi2 = Application.Small(vDeltaMesi, 2)
If mesi1 = 0 Then 'non sicuro se è giusto o meno
Sheets("foglio1").Cells(r, 132) = "same time" 0
Else
Sheets("foglio1").Cells(r, 132) = (mesi1 + mesi2) / 2
End If
If mesi2 = 0 Then 'non sicuro se è giusto o meno
Sheets("foglio1").Cells(r, 132) = "same time"
Else
Sheets("foglio1").Cells(r, 132) = (mesi1 + mesi2) / 2
End If

Next r
Next q


End Function


[/VBA]

So, I don't know why it doesn't work with Application.small! Thank you so much for reading this and helping me out guys!!!
Paul, basically you have written a code instead of the Application.small function, am I wrong?

Thank you
iMak

p.s. when I don't have the info about the month the database uses "-99" and I decided to intend it as january, that's why inside the code -99=1
p.p.s. I am a newbie to VBA, I am sorry if my code is not so efficient or too long!

[Paul_Hossler]

I was going by your initial comment:

I am looking for a code that, in an array of numbers, ordinate it in another array from the smallest number to the biggest, do you have any suggestions?

[vba]
Function SortedRange(r As Range) As Variant
[/vba]

Takes an input Range and returns a sorted array. The first element is the lowest, etc.

It is also possible to that the output array and return it to the worksheet.

Paul

[imakaveli]

I was going by your initial comment:




[vba]
Function SortedRange(r As Range) As Variant
[/vba]

Takes an input Range and returns a sorted array. The first element is the lowest, etc.

It is also possible to that the output array and return it to the worksheet.

Paul

Excuse me Paul, but isn't the code you posted a declaration of a variable? Or it is the formula to order my virtual array? Excuse me if this is a stupid question but I'm quite confused

[Paul_Hossler]

Sorry -if you look in the WB I attached:

1. the SortedRange function is on the worksheet as an array formula to display the sorted input range

2. it can also be used within VBA to return a variant (array) containing the sorted input range. That's what Sub Test does.

Maybe you can post a small sample with what you have and what you're looking for, so that other might have ideas / suggestions also

Paul