Solved

VBA Sorting array based on priority list

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

680 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