Sort multiple arrays using a sort by date function

Question

I have three arrays, DueDateArr, MilestoneDollarsArr, MilestoneNameArr. I wish to sort DueDateArr chronologically and using the same sorting procedure also sort the other arrays in the same order. I used How can I sort dates in an array in vba? with additional array sorting parts but this doesn't seem to work correctly. In the output everything is ok except for the first entry being the wrong date.

Alternatively if its possible I'd like to use something like a linked list that they have in java that is a sortable multiple dimensional array with different variable types.

Data is as follows: Sorted data is as follows: (note first entry is incorrect)

Dim TotalCountMinusOneForArrays as Integer
Dim DueDateArr() As Date
Dim MilestoneDollarsArr() As Double
Dim MilestoneNameArr() As String
Dim DueDateValue As Date
Dim MilestoneNameValue As String
Dim DueDateInfo As Date
Dim MilestoneDollarsInfo As Double
Dim MilestoneNameInfo As String
Dim i As Long, j As Long


i = 0
j = 0

For j = 2 To TotalCountMinusOneForArrays
    DueDateInfo = DueDateArr(j)
    MilestoneDollarsInfo = MilestoneDollarsArr(j)
    MilestoneNameInfo = MilestoneNameArr(j)

    For i = j - 1 To 1 Step -1
        If (DueDateArr(i) <= DueDateInfo) Then GoTo Sort
            DueDateArr(i + 1) = DueDateArr(i)
            MilestoneDollarsArr(i + 1) = MilestoneDollarsArr(i)
            MilestoneNameArr(i + 1) = MilestoneNameArr(i)
    Next i
    i = 0
Sort:   DueDateArr(i + 1) = DueDateInfo
    MilestoneDollarsArr(i + 1) = MilestoneDollarsInfo
    MilestoneNameArr(i + 1) = MilestoneNameInfo
    Next j

Tate Garringer · Accepted Answer · 2019-01-11 14:17:13Z

0

The simple approach would be to programmatically sort your data first using built-in sort functionality and then populate the array. However, when that is not an option, the two popular solutions are Bubble Sort or Merge Sort

Bubble sort being the easiest to apply:

Do Until bSort = False
    bSort = False
    For i = 0 to UBound(ArrToSort) - 1
        If ArrToSort(i + 1) < ArrToSort(i) Then
            tempVal = ArrToSort(i)
            ArrToSort(i) = ArrToSort(i + 1)
            ArrToSort(i + 1) = tempVal
            bSort = True
        End If
    Next i
Loop

For your case, if you wanted to do it multidimensionally instead of with several arrays you could do this

Do Until bSort = False
    bSort = False
    For i = 0 to UBound(ArrToSort) - 1
        If CDate(ArrToSort(i + 1, 1)) < CDate(ArrToSort(i, 1)) Then
            for i2 = 1 to 3
                tempVal(1, i2) = ArrToSort(i, i2)
                ArrToSort(i, i2) = ArrToSort(i + 1, i2)
                ArrToSort(i + 1) = tempVal(1, i2)
            next i2
            bSort = True
        End If
    Next i
Loop

Where ArrToSort(i, 1) is your date data in your multidimensional array.

EDIT:

Worth mentioning, to my knowledge there sadly is no fast way to sort arrays in excel VBA other than the methods provided.

EDIT 2:

Added CDate() around the date values of the array in the Bubble Sort.

edited Jan 11, 2019 at 14:17

answered Jan 11, 2019 at 1:36

Tate Garringer

1,5291 gold badge8 silver badges9 bronze badges

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

3 Comments

LambentDelphian Over a year ago

I tried the bubble sort but the sorting doesn't appear to work correctly with dates.

Tate Garringer Over a year ago

Have you tried adding CDate() around the date values? I can see that they're not in a typical date format, so you may have to tweak them programmatically during the array population process.

Tate Garringer Over a year ago

I've added it to my answer.

Collectives™ on Stack Overflow

Sort multiple arrays using a sort by date function

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related