• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1027
  • Last Modified:

VBA Sorting array based on priority list

I am loading a list of zones and values into an variant array (lets say called vZones(), with dimension 0 as zones and 1 as values), which looks like below
zones         value
--------        -------
A               13.2
B                231.2
CA             4545.00
AF               45.55
 
I have another table which defines the priority of each zone

Zone Priority
------ -------
AF      1
CA      2
B        3
A        4

I want the entries in vZones() to be sorted based on this priority table (in asc order)  when output to excel ws.

0
cynx
Asked:
cynx
  • 3
  • 2
2 Solutions
 
Eoin RyanI.T. ContractorCommented:
does this hit the excel sheet at all or are you loading from a file?

you could introduce a 3rd column, priority, then do a sort by priority and value, as you've already got the priority values.

Just bring them all together in the sheet and sort. Then read into the array if you still need it for other functions etc.
0
 
cynxAuthor Commented:
I did consider this approach, however the prob is i cant sort it where its stored, due to other info present there, and I will need to copy across to temp sheet to do above, I would rather prefer an algorithm to sort it in array and o/p the sorted data.
0
 
Eoin RyanI.T. ContractorCommented:
Hello,

You'll have to do a replacement of your letter codes with the corresponding number for your 2nd dimension in the array, sort it, then do a replacement of the numbers back to their codes when outputting.

Either that or create a 3rd line in your array for your priority, but then I don't know how the code would react. Best keep it at 2d for the code attached.

I've attached code, not my own. Let me know if it works out.
'BubbleSortNumbers 2D array(arrayXY)
Dim First As Integer, Last As Integer 
Dim FirstCol As Integer, LastCol As Integer 
 
Dim lTemp As Single 
Dim j As Integer, i As Integer, k As Integer 
 
First = LBound(arrayXY, 1) 
Last = UBound(arrayXY, 1) 
FirstCol = LBound(arrayXY, 2) 
LastCol = UBound(arrayXY, 2) 
 
For i = First To Last - 1 
    For j = i + 1 To Last 
        If arrayXY(i, 1) > arrayXY(j, 1) Then 
            For k = FirstCol To LastCol 
                lTemp = arrayXY(j, k) 
                arrayXY(j, k) = arrayXY(i, k) 
                arrayXY(i, k) = lTemp 
            Next k 
        End If 
    Next j 
Next i

Open in new window

0
 
cynxAuthor Commented:
HI, I used attached function to sort 3rd dimension of array. I mapped 3rd dimension of array via .Find function and looping thru array.
Thnx for pointing me to right direction.
Function BubbleSort(TempArray() As Variant, SortIndex As Long)
    Dim blnNoSwaps As Boolean
    Dim lngItem As Long
    Dim vntTemp(0 To 2) As Variant
    Dim lngCol As Long
    
    Do
        blnNoSwaps = True
        For lngItem = LBound(TempArray, 2) To UBound(TempArray, 2) - 1
            If TempArray(SortIndex, lngItem) > TempArray(SortIndex, lngItem + 1) Then
                blnNoSwaps = False
                For lngCol = 0 To 2
                    vntTemp(lngCol) = TempArray(lngCol, lngItem)
                    TempArray(lngCol, lngItem) = TempArray(lngCol, lngItem + 1)
                    TempArray(lngCol, lngItem + 1) = vntTemp(lngCol)
                Next
            End If
        Next
    Loop While Not blnNoSwaps

End Function

Open in new window

0
 
cynxAuthor Commented:
PS: Modified bubblesort function.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now