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

    Sort numbers across multiple columns! (Excel 2000>)

    Is this at all possible. To sort numbers ascending across multiple columns as in the sample file?
    Regards,
    Rudi

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

    Re: Sort numbers across multiple columns! (Excel 2000>)

    You can try this formula in I2:N20:

    =SMALL($B$2:$G$20,ROW()+19*(COLUMN()-9)-1)

    19 is the number of rows in the range B2:G20, and 9 is the column number of the first column in the target range (column I)

  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 numbers across multiple columns! (Excel 2000>)

    Well blow me away! Here I'm becoming boxing in with thinking LBound/Ubound Arrays, and you come with a interesting formula! Cheers Hans... you have officially <img src=/S/drop.gif border=0 alt=drop width=23 height=23> me today!

    PS: Now what formula can you construct to make Excel wash the dishes for me????
    Regards,
    Rudi

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort numbers across multiple columns! (Excel 2000>)

    <pre>Option Explicit

    Public Function Wash(Dishes As Range)
    Dishes.Clear
    End Function
    </pre>



    Name the range to be washed Dishes and:

    <pre>=Wash(Dishes)
    </pre>


    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Yes, I know it will get an error.
    Legare Coleman

  5. #5
    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 numbers across multiple columns! (Excel 2000>)

    Here is an approach that just sorts the list. It works with values other than numbers as well. It takes the list, makes 1 big list, sorts it (in a temp wks), then replaces it in the original spot.

    Steve

    <pre>Option Explicit
    Sub SortMultColList()
    Dim wks As Worksheet
    Dim rng As Range
    Dim rSource As Range
    Dim vList() As Variant
    Dim vList1 As Variant
    Dim vList2 As Variant
    Dim lRow As Long
    Dim iCol As Integer
    Dim x As Long
    Dim lRows As Long
    Dim iCols As Integer
    Dim lCount As Long
    Set rSource = Selection
    vList2 = rSource.Value
    lRows = UBound(vList2, 1)
    iCols = UBound(vList2, 2)
    lCount = lRows * iCols
    If lCount > 65536 Then
    MsgBox "List too big"
    Exit Sub
    End If
    Set wks = Worksheets.Add
    ReDim vList(1 To lCount, 0)
    x = 1
    For iCol = 1 To iCols
    For lRow = 1 To lRows
    vList(x, 0) = vList2(lRow, iCol)
    x = x + 1
    Next
    Next
    With wks
    Set rng = .Range(.Range("a1"), .Cells(lCount, 1))
    rng = vList
    rng.Sort .Range("a1"), xlAscending
    End With
    vList1 = rng.Value
    x = 1
    For iCol = 1 To iCols
    For lRow = 1 To lRows
    vList2(lRow, iCol) = vList1(x, 1)
    x = x + 1
    Next
    Next
    rSource = vList2
    Application.DisplayAlerts = False
    wks.Delete
    Application.DisplayAlerts = True

    Set rng = Nothing
    Set rSource = Nothing
    Set wks = Nothing</pre>

    End Sub

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort numbers across multiple columns! (Excel 2

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>
    Gre

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

    Re: Sort numbers across multiple columns! (Excel 2000>)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> Legare... If only it were that easy!!!

    Steve, what else could I expect than to get a answer via code...you've done it so many times in the past for me!
    I will study this up during the week and see what it does.
    Thanx for the reply!!
    Regards,
    Rudi

Posting Permissions

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