Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA Sorting array based on priority list

Posted on 2011-09-28
5
Medium Priority
?
979 Views
Last Modified: 2012-05-12
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
Comment
Question by:cynx
  • 3
  • 2
5 Comments
 
LVL 7

Expert Comment

by:Eoin Ryan
ID: 36716040
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
 
LVL 1

Author Comment

by:cynx
ID: 36716184
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
 
LVL 7

Accepted Solution

by:
Eoin Ryan earned 2000 total points
ID: 36813742
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
 
LVL 1

Assisted Solution

by:cynx
cynx earned 0 total points
ID: 36814531
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
 
LVL 1

Author Closing Comment

by:cynx
ID: 36908399
PS: Modified bubblesort function.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question