Solved

VBA Sorting array based on priority list

Posted on 2011-09-28
5
745 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:EoDawg
Comment Utility
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
Comment Utility
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:
EoDawg earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
PS: Modified bubblesort function.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

771 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

10 Experts available now in Live!

Get 1:1 Help Now