Solved

Sort a multi-dimensional (4 column) array

Posted on 2012-04-03
10
237 Views
Last Modified: 2012-04-03
I need to sort a multi-dimension array that has 4 columns,

The array is created in VBA based on a dynamic named range.

I got all the way of creating the array from the named range.

That attached file better shows what I'm trying to accomplish.
Book1.xls
0
Comment
Question by:mcnuttlaw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 37802289
Private Sub CommandButton1_Click()
DoSort
End Sub

Open in new window


In Module1


Sub DoSort()

    Range("A:D").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A:D").Select
    ActiveSheet.Paste
    Columns("C:C").Select
    Application.CutCopyMode = False
    Range("A:D").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:= _
        xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

Open in new window

0
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 37802831
Need to sort a named range.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 37802905
So are you saying that my code didn't work?
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 2

Author Comment

by:mcnuttlaw
ID: 37803461
I don't want to select the A:D columns because there are hundreds of rows before and after the named range (see my example file) that should not even be included in the sort.

I only want to sort the named range that can be in the middle of the sheet.
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 37803620
I think this will work, I did it the long way but i know it works. Let me know if you want it different.

Option Explicit

Sub calcResults()
    Dim ResultSheet As Worksheet
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim lastRow As Long
    Dim myArray As Variant
   
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Sheet1")
   
    lastRow = wks.Range("B" & wks.Rows.Count).End(xlUp).Row
   
    ThisWorkbook.Names.Add Name:="Results", RefersTo:=wks.Range("A" & (lastRow + 1) & ":D" & (lastRow + 18)), Visible:=True
   
    myArray = Application.Transpose(Range("Results"))
    'added from here down
    Dim Id() As Long
    Dim mI As Long
    ReDim Id(UBound(myArray, 2))
    For mI = 1 To UBound(myArray, 2)
        Id(mI) = mI
    Next
    ShellSort Id, myArray
    ThisWorkbook.Worksheets.Add
    Set ResultSheet = Worksheets.Add
    ResultSheet.Name = "Results"
    With ThisWorkbook.Sheets("Results")
        For mI = 1 To UBound(myArray, 2)
            .Cells(mI, 1) = myArray(1, Id(mI))
            .Cells(mI, 2) = myArray(2, Id(mI))
            .Cells(mI, 3) = myArray(3, Id(mI))
            .Cells(mI, 4) = myArray(4, Id(mI))
        Next
    End With
End Sub
Private Sub ShellSort(iIds&(), Arr)
    Dim mK&
    Dim I As Integer, MaxLimit&
    Dim J3 As Integer
    MaxLimit = UBound(Arr, 2)
    For I = 1 To MaxLimit - 1
        For J3 = I + 1 To MaxLimit
            If Arr(3, iIds(J3)) <= Arr(3, iIds(I)) Then
                mK = iIds(J3)
                iIds(J3) = iIds(I)
                iIds(I) = mK
            End If
        Next
    Next
End Sub
0
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 37803692
It does work.

One more question.

Possible to exlude any rows with a zero value from the sort into the Results tab?
0
 
LVL 14

Accepted Solution

by:
Tommy Kinard earned 500 total points
ID: 37803700
Well Of course! Keep in mind it is sorting on column 3 only.

Option Explicit

Sub calcResults()
    Dim ResultSheet As Worksheet
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim lastRow As Long
    Dim myArray As Variant
   
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Sheet1")
   
    lastRow = wks.Range("B" & wks.Rows.Count).End(xlUp).Row
   
    ThisWorkbook.Names.Add Name:="Results", RefersTo:=wks.Range("A" & (lastRow + 1) & ":D" & (lastRow + 18)), Visible:=True
   
    myArray = Application.Transpose(Range("Results"))
    'added from here down
    Dim Id() As Long
    Dim mI As Long, mN As Long
    ReDim Id(UBound(myArray, 2))
    For mI = 1 To UBound(myArray, 2)
        Id(mI) = mI
    Next
    ShellSort Id, myArray
    ThisWorkbook.Worksheets.Add
    Set ResultSheet = Worksheets.Add
    ResultSheet.Name = "Results"
    mN = 1
    With ThisWorkbook.Sheets("Results")
        For mI = 1 To UBound(myArray, 2)
            If myArray(3, Id(mI)) <> 0 Then
                .Cells(mN, 1) = myArray(1, Id(mI))
                .Cells(mN, 2) = myArray(2, Id(mI))
                .Cells(mN, 3) = myArray(3, Id(mI))
                .Cells(mN, 4) = myArray(4, Id(mI))
                mN = mN + 1
            End If
        Next
    End With
End Sub
Private Sub ShellSort(iIds&(), Arr)
    Dim mK&
    Dim I As Integer, MaxLimit&
    Dim J3 As Integer
    MaxLimit = UBound(Arr, 2)
    For I = 1 To MaxLimit - 1
        For J3 = I + 1 To MaxLimit
            If Arr(3, iIds(J3)) <= Arr(3, iIds(I)) Then
                mK = iIds(J3)
                iIds(J3) = iIds(I)
                iIds(I) = mK
            End If
        Next
    Next
End Sub
0
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 37803804
Very nice.

One more question.

Is there a variable to set to sort either ascending or descending order?
0
 
LVL 14

Assisted Solution

by:Tommy Kinard
Tommy Kinard earned 500 total points
ID: 37803841
If Arr(3, iIds(J3)) <= Arr(3, iIds(I)) Then  'Ascending
 If Arr(3, iIds(J3)) >= Arr(3, iIds(I)) Then 'Descending

I revised the sort routine to include the up or down choice AND you can choose which column to sort on, if it is column 3 you don't have to enter the column number of course.

Private Sub ShellSort(iIds&(), Arr, Up As Boolean, Optional Clmn As Long = 3)
    Dim mK&
    Dim I As Integer, MaxLimit&
    Dim J3 As Integer
    MaxLimit = UBound(Arr, 2)
    For I = 1 To MaxLimit - 1
        For J3 = I + 1 To MaxLimit
            If Up Then
                If Arr(Clmn, iIds(J3)) <= Arr(Clmn, iIds(I)) Then
                    mK = iIds(J3)
                    iIds(J3) = iIds(I)
                    iIds(I) = mK
                End If
            Else
                If Arr(Clmn, iIds(J3)) >= Arr(Clmn, iIds(I)) Then
                    mK = iIds(J3)
                    iIds(J3) = iIds(I)
                    iIds(I) = mK
                End If
           
            End If
        Next
    Next
End Sub
0
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 37803872
This is exactly what I was looking for.  Thank you.


btw - if you ever come up with the "short" version, be sure to let me know.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

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!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

734 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