Solved

VBA Sorting array based on priority list

Posted on 2011-09-28
5
774 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 6

Expert Comment

by:Eoin
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 6

Accepted Solution

by:
Eoin earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

914 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now