Sort Arrays ...

[krishnak]

Hi All,

I have two sets of arrays with related values and different data types.
NumArr(4) = {22,17,24,16}
StrArr(4) = {"A","D","C","B"}
These values are derived from the spreadsheet, where "A" has a value of 22, "B" has a value of 17 etc.
When I want to plot these values, assigning SeriesValues to NumArr and SeriesLabels to StrArr, I get an uneven column chart. I want to display a smooth ascending column chart by sorting NumArr. But by so doing, I should ensure that the order of the string values in the StrArr is also changed accordingly, so that the original relationship holds.
This means that NumArr(1) corresponds to StrArr(1) always and so on for other indexes.
I thought of using a 2-dimensional array, but the data types of the two variables are different.
Is there anyway I can do it by VBA?
Any suggestion will be appreciated.

[mancubus]

perhaps

[VBA]
Sub sort_arr()
'http://www.cpearson.com/excel/SortingArrays.aspx

Dim ws As Worksheet
Dim R As Range
Dim Arr As Variant

Application.ScreenUpdating = False

Set ws = ThisWorkbook.Worksheets.Add
Set R = ws.Range("A1").Resize(UBound(myArray) - LBound(myArray) + 1, 2)

Arr = [{"A",22;"D",17;"C",24;"B",16}]
R = Arr
R.Sort key1:=R.Columns(2), order1:=xlAscending, MatchCase:=False
Arr = R

Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub
[/VBA]

[mancubus]

you can test the code with this:

[VBA]
Sub test()

Dim ws
Dim R1 As Range, R2 As Range
Dim Arr As Variant

Set ws = ThisWorkbook.Worksheets.Add

Arr = [{"A",22;"D",17;"C",24;"B",16}]

Set R1 = ws.Range("A1").Resize(UBound(Arr) - LBound(Arr) + 1, 2)
R1 = Arr
R1.Sort key1:=R1.Columns(2), order1:=xlAscending, MatchCase:=False
Arr = R1

Set R2 = ws.Range("G1").Resize(UBound(Arr) - LBound(Arr) + 1, 2)
R2 = Arr

End Sub
[/VBA]

[krishnak]

Hello mancubus,

Thanks for posting the solution. This will create a new worksheet, where the values will be pasted and sorted.

For the specific purpose, I tried the following subroutine.
[VBA]
Sub SortTwoRelatedArrays(arNum() As Double, arStr() As String)
Dim nOne As Double, nTwo As Double
Dim strOne As String, strTwo As String
Dim i As Integer, j As Integer
'Check that the number of elements in both arrays are equal.
If (UBound(arNum) <> UBound(arStr)) Then
MsgBox ("Arrays DO NOT contain same number of elements.")
Exit Sub
End If

For i = 1 To UBound(arNum)
For j = i To UBound(arNum)
If (arNum(j) < arNum(i)) Then
nOne = arNum(i)
nTwo = arNum(j)
strOne = arStr(i)
strTwo = arStr(j)
arNum(i) = nTwo
arNum(j) = nOne
arStr(i) = strTwo
arStr(j) = strOne
End If
Next j
Next i

End Sub
[/VBA]

[mancubus]

this works for me.

[VBA]
Sub arr_sort()
'http://excelwiki.com/vba-arrays/bubble-sort-in-vba

Dim aNum(), aStr(), Arr(), t
Dim i As Long, j As Long, UB As Long, y As Long

aNum = Array(22, 17, 24, 16)
aStr = Array("A", "D", "C", "B")

If (UBound(aNum) <> UBound(aStr)) Then
MsgBox ("Arrays DO NOT contain same number of elements.")
Exit Sub
End If

UB = UBound(aNum) - LBound(aNum) + 1

For i = LBound(aNum) To UBound(aNum)
j = j + 1
ReDim Preserve Arr(1 To UB, 1 To 2)
Arr(j, 1) = aNum(i) 'aNum : col 1
Arr(j, 2) = aStr(i)
Next i

Range("A1:B4") = Arr 'to see unsorted array values

SortColumn = 1 'aNum : col 1
For i = LBound(Arr, 1) To UBound(Arr, 1) - 1
For j = LBound(Arr, 1) To UBound(Arr, 1) - 1
Condition = Arr(j, SortColumn) > Arr(j + 1, SortColumn)
If Condition Then
For y = LBound(Arr, 2) To UBound(Arr, 2)
t = Arr(j, y)
Arr(j, y) = Arr(j + 1, y)
Arr(j + 1, y) = t
Next y
End If
Next j
Next i

Range("A11:B14") = Arr 'to see sorted array values

End Sub
[/VBA]

[Chabu]

You can also use a variant array then you can put anythin in it and sort the array directly.

[Chabu]

Theats what you did already, (need to check my eyes)

[krishnak]

Hello mancubus,

Basically we are creating a 2-dimensional array with data types as variants. I did not think of the 'variant' data type.
Since it is easy to understand, I thought of using the classic 'bubble sort' on one array and sort the values simultaneously in the second array.

As my application is for sorting the values for the coordinates for a chart (small set of values), that suited me quite well.

However, it is a good to know that there are alternatives and thank you for posting the solution.

[mancubus]

you're wellcome.