Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort more than 3 columns macro (2000)

    Is there a macro that allows you to sort more than 3 columns?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort more than 3 columns macro (2000)

    You can record such a macro or write it. The idea - whether you sort interactively or in a macro - is to sort on the three least important keys first, then on the (up to) three slightly more important keys, etc., and finally sort on the (up to) three most important keys.

    Here is an example that sorts on columns A to G:

    Sub SortMultiple()
    Range("A1:G100").Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range( _
    "F2"), Order2:=xlAscending, Key3:=Range("G2"), Order3:=xlAscending, _
    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
    xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
    DataOption3:=xlSortNormal
    Range("A1:G100").Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range( _
    "C2"), Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, _
    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
    xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
    DataOption3:=xlSortNormal
    Range("A1:G100").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    End Sub

    Some arguments to Sort are superfluous; the above code is the unmodified macro recorder code.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort more than 3 columns macro (2000)

    You can use the A>Z and Z>A buttons on the toolbar to perform a consecutive sort quite easily! As Hans explained in the previous thread, you MUST start the sort on the least important column first, working your way up to the most important column. You can (in this way) sort MUCH more that 3 columns. Please bear in mind that the data must relate to each other. IE, sort first the name, then the surname, then the company, then the province(state), then the country, then the continent, then the planet, then the milky way, then the star system, then the universe....(hope you get my point)!
    You can simply record this sort order into a macro, if you need to perform it regularly!
    Regards,
    Rudi

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sort more than 3 columns macro (2000)

    Here is routine that sorts a 2-dimensional array. You can read the array directly from an excel range and then when done, put it back into the excel range.

    Steve

    <pre>Sub BubbleSort2(vList(), ParamArray vSortCols())
    ' Sorts a Two-dimensinal array using bubble sort algorithm
    ' Sorts on multiple columns
    '
    ' vSortCols contains an array of Pairs of values:
    ' Column number to sort and logical values:
    ' TRUE = ascending, FALSE = descending
    ' if the final SortOrder is not listed:
    ' it is assumed to be TRUE (Ascending sort)
    '
    ' Usage example:
    ' Call BubbleSort2(vArray, 2, True, 1, False, 3)
    '
    ' This will sort the array named vArray
    ' The primary sort is on col 2 of the array and it will
    ' sorted in ASCENDING order
    ' if there is a "tie" in col 2, it will check
    ' Col 1 in DESCENDING order
    ' if there is a tie in BOTH columns 1 and 2 then it will
    ' check in col 3 and sort Ascending (the default)

    Dim iFirst As Integer, iLast As Long
    Dim iFirstCol As Integer, iLastCol As Integer
    Dim i As Long, j As Long
    Dim x As Long, y As Long
    Dim z As Integer
    Dim k As Integer, l As Integer
    Dim iMultiSort As Integer
    Dim vTemp()
    Dim CompLo, CompHi
    Dim bSortX As Boolean
    Dim iCol As Integer
    Dim iSortCol As Integer
    Dim bAscending As Boolean

    iMultiSort = UBound(vSortCols)
    iSortCol = 1
    If iMultiSort >= 0 Then _
    iSortCol = vSortCols(0)

    bAscending = True
    If iMultiSort >= 1 Then _
    bAscending = vSortCols(1)

    iFirst = LBound(vList, 1)
    iLast = UBound(vList, 1)
    iFirstCol = LBound(vList, 2)
    iLastCol = UBound(vList, 2)

    ReDim vTemp(iFirstCol To iLastCol)
    For i = iFirst To iLast - 1
    For j = i + 1 To iLast
    x = IIf(bAscending, i, j)
    y = IIf(bAscending, j, i)

    If vList(x, iSortCol) > vList(y, iSortCol) Then
    For iCol = iFirstCol To iLastCol
    vTemp(iCol) = vList(y, iCol)
    vList(y, iCol) = vList(x, iCol)
    vList(x, iCol) = vTemp(iCol)
    Next
    ElseIf vList(x, iSortCol) = vList(y, iSortCol) Then
    For z = 2 To iMultiSort Step 2
    bSortX = True
    If iMultiSort > z Then _
    bSortX = vSortCols(z + 1)
    k = IIf(bSortX, i, j)
    l = IIf(bSortX, j, i)

    CompLo = vList(k, vSortCols(z))
    CompHi = vList(l, vSortCols(z))
    If CompLo > CompHi Then
    For iCol = iFirstCol To iLastCol
    vTemp(iCol) = vList(l, iCol)
    vList(l, iCol) = vList(k, iCol)
    vList(k, iCol) = vTemp(iCol)
    Next
    Exit For
    ElseIf CompLo < CompHi Then
    Exit For
    End If
    Next
    End If
    Next j
    Next i
    End Sub</pre>


  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort more than 3 columns macro (2000)

    Steve,

    I'm interested in comparing methods of sorting. It seems that you often use arrays in dealing with sets of data.
    I have put together a wsh with 2.5+ million peices of data (39 fields, 65000+ rows -records). I then have a named range
    specifying the field names that I want sorted along with Asc/Desc order.
    I use something similar to Hans VBA suggestion above to then do Advanced Filter and/or Sort the dataset. Rather than sort in groups of 3 keys I simply iterate the sort from least to most- significant. (I may be able to speed it up by combining, but haven't taken the time yet to put that together).

    The fields contain data types
    integers
    Long
    Date
    TExt
    Boolean

    I installed a Timer in the code to give me feedback with the following approx results. How does this compare with sorts that you may
    have done using arrays then returning the data to an xl range

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center valign=bottom>1</td><td valign=bottom>Num of Sorts</td><td valign=bottom>Time (Sec.)</td><td align=center valign=bottom>2</td><td align=right valign=bottom>1</td><td align=right valign=bottom>1.8</td><td align=center valign=bottom>3</td><td align=right valign=bottom>2</td><td align=right valign=bottom>3.4</td><td align=center valign=bottom>4</td><td align=right valign=bottom>3</td><td align=right valign=bottom>5.1</td><td align=center valign=bottom>5</td><td align=right valign=bottom>4</td><td align=right valign=bottom>6.7</td><td align=center valign=bottom>6</td><td align=right valign=bottom>5</td><td align=right valign=bottom>8.5</td><td align=center valign=bottom>7</td><td align=right valign=bottom>6</td><td align=right valign=bottom>10.3</td><td align=center valign=bottom>8</td><td align=right valign=bottom>7</td><td align=right valign=bottom>11.8</td></table>

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sort more than 3 columns macro (2000)

    In general, if you use a builtin routine directly from VB or excel rather than using a macro routine the results will be faster.

    Using Arrays is many times faster than working with excel from VB. Reading a range (enmass) into an array and writing from an array to a range (enmass) is faster than doing it cell by cell.

    I have never used arrays as large as you suggest, but I would suggest you try the routine I wrote with your dataset and compare the results: I would be curious to hear of the results. Remember my code can do all the sorting in one fell swoop with all 39 columns.

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort more than 3 columns macro (2000)

    I know there is a way of copying your code and pasting directly into a module without the entire code ending up in one code line, but when I searched I didn't see the answer. Ialso checke FAQ.

    When I pasted into a wsh it looks fine, but how do I get it into a module?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort more than 3 columns macro (2000)

    Copy the code from a post to the clipboard.
    Paste it into Word or WordPad.
    Select the pasted code. It should look OK (not on one line)
    Copy it to the clipboard.
    Paste it into a module.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sort more than 3 columns macro (2000)

    In addition to Hans' suggestions:
    I just copy it into a blank worksheet page. Then copy from the worksheet page into VB

    No need to even use another program...

    Steve

  10. #10
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort more than 3 columns macro (2000)

    Thanks, I'll try this

    By the way, Steve in looking at your code:
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''
    Usage example:' Call BubbleSort2(vArray, 2, True, 1, False, 3)''
    This will sort the array named vArray
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''
    How do you get the vArray declared and filled with the data in an xl named range?
    Does this work?

    Dim vArray(MyRecords.Row.Count,MyRecords.Column.Count)


    The range MyRecords is the data-only (no headers) part of the range named MyDatabase (which includes the field names -col.headers)

    I assume that your code will then take care of reading data into the array and writing back after sorting.

  11. #11
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort more than 3 columns macro (2000)

    Ok. I got your code into a standard module, then wrote the following sub.
    I set up a set of test data and named the data range --- TestRecords.
    When I run this nothing happens to the records


    Sub SortList()

    Dim x As Long, y As Long
    Dim vArray()
    x = Range("TestRecords").Rows.count
    y = Range("TestRecords").Columns.count
    ReDim vArray(x, y)
    Call BubbleSort2(vArray, 4, True, 9)


    End Sub

    What do I need to change?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort more than 3 columns macro (2000)

    Your code only declares an array, but it neither gets data from the spreadsheet, nor writes them back to it.

    If you change the first line of Steve's procedure to use a variant instead of a dynamic array:

    Sub BubbleSort2(vList As Variant, ParamArray vSortCols())

    you can use it like this:

    Sub SortList()
    Dim vArray As Variant
    vArray = Range("TestRecords")
    BubbleSort2 vArray, 4, True, 9
    Range("TestRecords") = vArray
    Erase vArray
    End Sub

  13. #13
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort more than 3 columns macro (2000)

    Ok. Now that works. Great
    Now, lets take it one step further

    I have a range named SortBy in B3:C11 where the user can enter the sort Keys and order (Asc/Desc)
    I'd like to declare another variant array, fed by range("SortBy") and use this new array as the argument in the code line:

    BubbleSort2 vArray, <font color=red>4, True, 9</font color=red>

    (as in the red part.)
    How do I do this?
    Thanks


    <table border=1><td></td><td align=center>B</td><td align=center>C</td><tr><td align=center valign=bottom>1</td><td valign=bottom>SORT</td><td valign=bottom>

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort more than 3 columns macro (2000)

    You'll have to change the declaration of BubbleSort2 again, to use a variant/array as second argument instead of a ParamArray. The array can be populated from a range on the workbook. I have attached a sample workbook.

  15. #15
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort more than 3 columns macro (2000)

    Thanks Steve and Hans

    I now have a couple versions of the bubble sort plus an adaptation of Hans' suggestion in the 2nd post from the top (above) up and running. My adaptation takes a look at a "SortBy" range where it picks up the number of columns on which the user wishes to sort the data (plus the sort order for each field) and Loops thru the built-in excel sort process for each field (column).

    My goal was to "test the limits" so to speak, of using xl as a stand alone database platform.
    The test results so far have been a bit surprising to me: looping through xl's built-in sort has been FAR FASTER than reading the data into an array,sorting the array, then writing the data back to an xl range. The larger the dataset the more significant the difference. It also didn't take too long before the array method yielded an overflow error. In addition, when I compared the sort results, it appears that the least significant sort order was not sorted correctly using the array method.
    My test scenario:
    Because of the large number of records (65,000) many of the records were very similar and needed 5 levels of sorting to get the correct order. Sorting the full dataset hung the array method so I started with 1000 records and worked up.
    I put a timer into the code to make comparisons. By the time I reached 10,000 records, xl was still less than a second, while the array method was over 10 minutes. Here are the results:

    <table border=1><tr><td valign=bottom>Time in Seconds</td><td align=right valign=bottom>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •