Printable View
Hi
Want to sort an array based on another array just like Excel's custom sorting. Something like...
Code:Function SortArray(ByVal SortedArray As Variant, ByVal Array2Sort As Variant) As Variant
End Function
I wrote a function which does the trick, but looking for solutions from the experts :)
Kris
Please post a sample workbook in which you illustrate what you are after (situation to start with & desired result).
Are you talking about 1-dimensional or multidimensional arrays ?
Code:Sub M_snb()
sn = Split("aa bb ii cc dd aa ee cc ff gg hh ii")
sp = Split("ii hh gg ff ee dd cc bb aa")
MsgBox Trim(F_sort(sn, sp))
End Sub
Function F_sort(sq, st)
For j = 0 To UBound(st)
For jj = 0 To UBound(sq)
If sq(jj) = st(j) Then F_sort = F_sort & " " & sq(jj)
Next
Next
End Function
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
Thanks snb !
I wrote this one, but this doesn't return duplicates. At the time of writing this code, I haven't thought of duplicates :(
Code:Function SortArray(ByVal SortedArray As Variant, ByVal Array2Sort As Variant) As Variant
Dim Order(), x, i As Long
Const Delim As String = "|"
ReDim Order(UBound(SortedArray))
For i = LBound(SortedArray) To UBound(SortedArray)
Order(i) = Delim
Next
For i = LBound(Array2Sort) To UBound(Array2Sort)
x = Application.Match(Array2Sort(i), SortedArray, 0)
If Not IsError(x) Then
Order(x - 1) = Array2Sort(i)
End If
Next
SortArray = Filter(Order, Delim, False)
End Function
Hi Admin,
I was very confused by your terminology as to what you wanted.
Once I guessed what you wanted, I did a code... only to find it was just a variation of snb’s !!
Never mind! _...
:)
_....Some code explanations for any novice like me catching the Thread!
:)
Alan
Code:Sub MesnbVersion() ' http://www.excelfox.com/forum/showthread.php/2130-Sort-an-array-based-on-another-array-VBA?p=9972#post9972
Dim sn() As String, sp() As String ' VBA Strings Split Function used below which returns String types
sn() = Split("aa bb ii cc dd aa ee cc ff gg hh ii") 'This Array is an Array to be sorted in an order which depends on its Element values
sp() = Split("ii hh gg ff ee dd cc bb aa") 'This Array shows the order in which the Element values should be made
Dim Results() As String 'Arrays into the Function and out will have String type Elements ( are "housed" in transport in a variant Variable *** )
Let Results() = SortInOrder(sn, sp) ' ( Array 2 be sorted in Order , Array showing wanted order )
MsgBox Prompt:=VBA.Strings.Join(Results, " ")
MsgBox Trim(F_sort(sn, sp))
End Sub
' ( Array 2 be sorted in Order , Array showing wanted order )
Function SortInOrder(ByVal Array2BSorted As Variant, ByVal ArrayOrder2Sort As Variant) As Variant ' For ByVal Arrays must be "housed in Variant Variables. *** http://www.excelforum.com/showthread.php?t=1101544&page=10#post4381420 The only Type that can return an Array is variant so that must be the type of the Function
Dim IndxA2B As Long, IndxOrdr As Long, IndxASInOdr As Long: Let IndxASInOdr = -1 ' ' Index for Array to be sorted, Array showing order, and final sorted Array: Index for final sorted Array will be incremented by 1 just before next entry, and we are using base 0 1 Dimensional Arrays throughout, so before the first increment it should have -1 so that adding 1 gives zero, the first required index in the Array
Dim arrSortInOrder() As String: ReDim arrSortInOrder(LBound(Array2BSorted) To UBound(Array2BSorted)) ' !!! All Entries in the Array to be sorted are used , including duplicates in the Final Array, so the Outup Sorted Array will have the same size as the Input Array to be sorted
For IndxOrdr = LBound(ArrayOrder2Sort) To UBound(ArrayOrder2Sort) 'In order of the Sorted order , IndxOrdr ...
For IndxA2B = LBound(Array2BSorted) To UBound(Array2BSorted) '...each Element in the Array to be sorted is considered and...
If Array2BSorted(IndxA2B) = ArrayOrder2Sort(IndxOrdr) Then '... when we find it ( we will eventually !!! ) we
Let IndxASInOdr = IndxASInOdr + 1 '... increase the index in the final sorted Array, and...
Let arrSortInOrder(IndxASInOdr) = Array2BSorted(IndxA2B) 'put the found matched Element in the Final Sorted Array. As we go in to the next Element in the Array to be sorted, we will catch any duplicate and that will be put in the next Element of the Final sorted Array untill all Elements are checked ..Then...we...
Else ' case of not found the current element to be sorted yet ( we always do !!! ) Redundant code line
End If
Next IndxA2B ' keep going along the Array to be sorted !!! - we will eventually find the Element with the value of the current Element considered from the Array showing order
Next IndxOrdr '... go back to look for all Elements in the Array to be sorted which match the next Element in the Order to be sorted Array
Let SortInOrder = arrSortInOrder() ' The final Array is put in the Variant variable SortInOrder ( effectively "returning" it at end of Function - or rather as the code moves on past the Function the Array is in that Variable - Function( ) as Variant is like Dim Function as Variant, and the course of the function does the Let Function = bit here )
End Function
Function F_sort(sq, st)
Dim j As Long, jj As Long
For j = 0 To UBound(st)
For jj = 0 To UBound(sq)
If sq(jj) = st(j) Then F_sort = F_sort & " " & sq(jj)
Next
Next
End Function
If you want the result in an array:
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHACode:Sub M_snb()
sn = Split("aa bb ii cc dd aa ee cc ff gg hh ii")
sp = Split("ii hh gg ff ee dd cc bb aa")
MsgBox Join(F_sort(sn, sp), vbLf)
End Sub
Function F_sort(sq, st)
sr = sq
y = 0
For j = 0 To UBound(st)
For jj = 0 To UBound(sq)
If sq(jj) = st(j) Then
sr(y) = sq(jj)
y = y + 1
End If
Next
Next
F_sort = sr
End Function
https://eileenslounge.com/viewtopic.php?p=318868#p318868
https://eileenslounge.com/viewtopic.php?p=318311#p318311
https://eileenslounge.com/viewtopic.php?p=318302#p318302
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317857#p317857
https://eileenslounge.com/viewtopic.php?p=317541#p317541
https://eileenslounge.com/viewtopic.php?p=317520#p317520
https://eileenslounge.com/viewtopic.php?p=317510#p317510
https://eileenslounge.com/viewtopic.php?p=317547#p317547
https://eileenslounge.com/viewtopic.php?p=317573#p317573
https://eileenslounge.com/viewtopic.php?p=317574#p317574
https://eileenslounge.com/viewtopic.php?p=317582#p317582
https://eileenslounge.com/viewtopic.php?p=317583#p317583
https://eileenslounge.com/viewtopic.php?p=317605#p317605
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316046#p316046
https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
https://www.youtube.com/@alanelston2330
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
https://eileenslounge.com/viewtopic.php?p=316154#p316154
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://eileenslounge.com/viewtopic.php?p=317050#p317050
https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316057#p316057
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=316705#p316705
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=176255#p176255
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
my code version --- just simpler and neater , as always :)Quote:
Originally Posted by snb
Doing away with all the .... Extra stuff... Lol..!
;)
Add an Arraylist version to the ..... List, collection, of ways
:rolleyes:
Function SortInOrderArrayListStylio(
Code:Sub MesnbVersion() ' http://www.excelfox.com/forum/showthread.php/2130-Sort-an-array-based-on-another-array-VBA?p=9972#post9972
Dim sn() As String, sp() As String ' VBA Strings Split Function used below which returns String types
sn() = Split("aa bb ii cc dd aa ee cc ff gg hh ii") 'This Array is an Array to be sorted in an order which depends on its Element values
sp() = Split("ii hh gg ff ee dd cc bb aa") 'This Array shows the order in which the Element values should be made
Dim Results() As String 'Arrays into the Function and out will have String type Elements ( are "housed" in transport in a variant Variable *** )
Let Results() = SortInOrder(sn, sp) ' ( Array 2 be sorted in Order , Array showing wanted order )
MsgBox Prompt:=VBA.Strings.Join(Results, " ")
MsgBox Trim(F_sort(sn, sp))
Dim VarResults() As Variant ' 1 dimensional Array returned by .ToArray Methhod returns Variant Elements
Let VarResults() = SortInOrderArrayListStylio(sn, sp) ' ( Array 2 be sorted in Order , Array showing wanted order )
MsgBox Prompt:=VBA.Strings.Join(Results, " ")
End Sub
' ( Array 2 be sorted in Order , Array showing wanted order )
Function SortInOrder(ByVal Array2BSorted As Variant, ByVal ArrayOrder2Sort As Variant) As Variant ' For ByVal Arrays must be "housed in Variant Variables. *** http://www.excelforum.com/showthread.php?t=1101544&page=10#post4381420 The only Type that can return an Array is variant so that must be the type of the Function
Dim IndxA2B As Long, IndxOrdr As Long, IndxASInOdr As Long: Let IndxASInOdr = -1 ' ' Index for Array to be sorted, Array showing order, and final sorted Array: Index for final sorted Array will be incremented by 1 just before next entry, and we are using base 0 1 Dimensional Arrays throughout, so before the first increment it should have -1 so that adding 1 gives zero, the first required index in the Array
Dim arrSortInOrder() As String: ReDim arrSortInOrder(LBound(Array2BSorted) To UBound(Array2BSorted)) ' !!! All Entries in the Array to be sorted are used , including duplicates in the Final Array, so the Output Sorted Array will have the same size as the Input Array to be sorted
For IndxOrdr = LBound(ArrayOrder2Sort) To UBound(ArrayOrder2Sort) 'In order of the Sorted order , IndxOrdr ...
For IndxA2B = LBound(Array2BSorted) To UBound(Array2BSorted) '...each Element in the Array to be sorted is considered and...
If Array2BSorted(IndxA2B) = ArrayOrder2Sort(IndxOrdr) Then '... when we find it ( we will eventually !!! ) we
Let IndxASInOdr = IndxASInOdr + 1 '... increase the index in the final sorted Array, and...
Let arrSortInOrder(IndxASInOdr) = Array2BSorted(IndxA2B) 'put the found matched Element in the Final Sorted Array. As we go in to the next Element in the Array to be sorted, we will catch any duplicate and that will be put in the next Element of the Final sorted Array untill all Elements are checked ..Then...we...
Else ' case of not found the current element to be sorted yet ( we always do !!! ) Redundant code line
End If
Next IndxA2B ' keep going along the Array to be sorted !!! - we will eventually find the Element with the value of the current Element considered from the Array showing order
Next IndxOrdr '... go back to look for all Elements in the Array to be sorted which match the next Element in the Order to be sorted Array
Let SortInOrder = arrSortInOrder() ' The final Array is put in the Variant variable SortInOrder ( effectively "returning" it at end of Function - or rather as the code moves on past the Function the Array is in that Variable - Function( ) as Variant is like Dim Function as Variant, and the course of the function does the Let Function = bit here )
End Function
Function F_sort(sq, st)
Dim j As Long, jj As Long
For j = 0 To UBound(st)
For jj = 0 To UBound(sq)
If sq(jj) = st(j) Then F_sort = F_sort & " " & sq(jj)
Next
Next
End Function
'http://www.snb-vba.eu/VBA_Arraylist_en.html
Function SortInOrderArrayListStylio(ByVal Array2BSorted As Variant, ByVal ArrayOrder2Sort As Variant) As Variant ' For ByVal Arrays must be "housed in Variant Variables. *** http://www.excelforum.com/showthread.php?t=1101544&page=10#post4381420 The only Type that can return an Array is Variant so that must be the type of the Function
Dim IndxA2B As Long, IndxOrdr As Long ' ' Index for Array to be sorted, Array showing order
Dim a_00 As Object
Set a_00 = CreateObject("System.Collections.ArrayList") 'Without a reference to the library: 'late binding' with the use of a variable: http://www.snb-vba.eu/VBA_Arraylist_en.html#L_4.2.1
For IndxOrdr = LBound(ArrayOrder2Sort) To UBound(ArrayOrder2Sort) ' In order of the Sorted order , IndxOrdr ...
For IndxA2B = LBound(Array2BSorted) To UBound(Array2BSorted) ' ...each Element in the Array to be sorted is considered and...
If Array2BSorted(IndxA2B) = ArrayOrder2Sort(IndxOrdr) Then '... when we find it ( we will eventually !!! ) we
a_00.Add (Array2BSorted(IndxA2B)) 'Add to the Array list - automatically at next position along
Else ' case of not found the current element to be sorted yet ( we always do !!! ) Redundant code line
End If
Next IndxA2B ' keep going along the Array to be sorted !!! - we will eventually find the Element with the value of the current Element considered from the Array showing order
Next IndxOrdr '.. . go back to look for all Elements in the Array to be sorted which match the next Element in the Order to be sorted Array
Let SortInOrderArrayListStylio = a_00.toarray() ' The method .ToArray writes all elements of the ArrayList into a 1-dimensional Array. ' http://www.snb-vba.eu/VBA_Arraylist_en.html#L_9.3 ( effectively "returning" it at end of Function - or rather as the code moves on past the Function the Array is in that Variable - Function( ) as Variant is like Dim Function as Variant, and the course of the function does the Let Function = bit here )
End Function
But why do i have to do this
a_00.Add (Array2BSorted(IndxA2B))
and not this
a_00.Add Array2BSorted(IndxA2B)
:confused: Hmmm...
_.. I have to "evaluate it" ?? to return its Value ??
'Rem Ref http://www.snb-vba.eu/VBA_Arraylist_en.html
' http://www.excelfox.com/forum/showth...=9991#post9991