VBA: Sort array based on another array

Question

I have several very long arrays which need to be sorted alphabetically based on user action, but for simplicity I'll use the following example:

Dim Name as Variant, Street as Variant
Name = array("B", "C", "D", "A", "E")
Street = array("1", "2", "3", "4", "5")

After the user clicks the sorting button, the Name array now has the following order

("A", "B", "C", "D", "E")

and I then need to sort the Street array, so it gets the corresponding order, i.e.

("4", "1", "2", "3", "5")

What is the most efficient way to do this in VBA?

Please notice: I know how to sort an individual array normally in VBA, I'm only looking for answers that involve sorting an array based on another array. Thanks.

Why not a 2D Array? How do you sort a normal Array? Do you have some code that does it? If so, please could you share that? — PaulFrancis
– PaulFrancis, Commented Apr 28, 2015 at 15:46
Sorting an array in VBA is hardly news, but since you ask, I use the excellent qsort algorithm by Chip Pearson for that, cpearson.com/excel/SortingArrays.aspx — Miqi180
– Miqi180, Commented Apr 28, 2015 at 16:13

Gary's Student · Accepted Answer · 2015-04-28 17:01:03Z

4

This appears to do what you want:

Sub MAIN()
    Dim Name(), Street()
    Name = Array("B", "C", "D", "A", "E")
    Street = Array("1", "2", "3", "4", "5")

    Call sort2(Name(), Street())

    For Each s In Street
        MsgBox s
    Next s
End Sub


Sub sort2(key() As Variant, other() As Variant)
Dim I As Long, J As Long, Low As Long
Dim Hi As Long, Temp As Variant
    Low = LBound(key)
    Hi = UBound(key)

    J = (Hi - Low + 1) \ 2
    Do While J > 0
        For I = Low To Hi - J
          If key(I) > key(I + J) Then
            Temp = key(I)
            key(I) = key(I + J)
            key(I + J) = Temp
            Temp = other(I)
            other(I) = other(I + J)
            other(I + J) = Temp
          End If
        Next I
        For I = Hi - J To Low Step -1
          If key(I) > key(I + J) Then
            Temp = key(I)
            key(I) = key(I + J)
            key(I + J) = Temp
            Temp = other(I)
            other(I) = other(I + J)
            other(I + J) = Temp
          End If
        Next I
        J = J \ 2
    Loop
End Sub

EDIT#1:

To add more arrays to the mix, just include them in the header and insert more lines like:

            Temp = other2(I)
            other2(I) = other2(I + J)
            other2(I + J) = Temp

in both places in the sort routine.

edited Apr 28, 2015 at 17:01

answered Apr 28, 2015 at 16:04

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Miqi180 Over a year ago

Looks interesting. I'll test and get back.

Miqi180 Over a year ago

Works well, I'll mark as accepted. What do you suppose would be the easiest way to extend this to work with several arrays to be sorted at once based on just one?

Gary's Student Over a year ago

@Miqi180..................If I had many arrays, I would be tempted to stuff them into worksheet columns and use Excel's native sort.

Miqi180 Over a year ago

Yeah, that would make the coding easier but I can't do that here. I need the arrays to be sorted as quickly as possible and accessing the document slows things down, even if you turn the screenupdating off.

Miqi180 Over a year ago

Just tested the augmented code with 5 arrays at once. Works great. Thanks!

|

Collectives™ on Stack Overflow

VBA: Sort array based on another array

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related