Results 1 to 7 of 7

Thread: Sort an array based on another array - VBA

  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10

    Sort an array based on another array - VBA

    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  2. #2
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    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
    Last edited by DocAElstein; 10-24-2023 at 02:59 PM.

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    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 are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  5. #5
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    If you want the result in an array:

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

  6. #6
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Quote Originally Posted by snb View Post
    If you want the result in an array:

    .....
    my code version --- just simpler and neater , as always
    Doing away with all the .... Extra stuff... Lol..!

    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  7. #7
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Add an Arraylist version to the ..... List, collection, of ways

    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)
    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
    Last edited by DocAElstein; 10-23-2016 at 02:20 PM. Reason: Add referrenxe - for future referrence !! ;)
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Similar Threads

  1. Replies: 9
    Last Post: 08-02-2013, 07:55 PM
  2. Replies: 14
    Last Post: 01-26-2013, 04:58 AM
  3. Replies: 1
    Last Post: 12-04-2012, 08:56 AM
  4. Custom Spin Button Based On Values Passed From Array
    By Preeti Verma in forum Excel Help
    Replies: 7
    Last Post: 05-22-2012, 07:23 PM
  5. VBA Function to Search in Array
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 04-10-2012, 11:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •