Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort Data (office xp)

    Hello, i would like to sort my data according to significance. i have columns to sort and sort according to a figure in each same row. which cell (in the same row) has the highest number will queue first in the first column and the smallest number queue the last column.

    if 'this' column has the highest number among all columns that need sort, this column with its first next column (its pair) will queue in the first column among the all the columns that need sort. this sorted column will take 2 columns because the second column is its pair. i can have 20 columns or more (without counting its pair) to sort in this task.

    attached is a sample file for your checking.

    can i know how can i do this in simple way?

    Thanks.

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

    Re: Sort Data (office xp)

    Perhaps there is a simple way, but here is a macro that sorts the table the way you want:

    Sub SortSales()
    Dim i As Long
    Dim iMax As Long
    Dim j As Long
    Dim jMax As Long
    Dim MaxSale As Double
    Dim rng As Range
    Dim lngRows As Long
    Set rng = Range("A3").CurrentRegion
    lngRows = rng.Rows.Count
    iMax = rng.Columns.Count
    For i = 2 To iMax - 3 Step 2
    MaxSale = rng.Cells(lngRows, i)
    jMax = i
    For j = i + 2 To iMax
    If Cells(9, j) > MaxSale Then
    MaxSale = rng.Cells(lngRows, j)
    jMax = j
    End If
    Next j
    If jMax > i Then
    rng.Cells(1, jMax).Resize(lngRows, 2).Cut
    rng.Cells(1, i).Resize(lngRows, 2).Insert Shift:=xlToRight
    End If
    Next i
    End Sub

  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 Data (office xp)

    Wow...this is one to step through! I was waiting for an answer for this post! I'll be studying up this one to see what you did.
    Nice one Hans!
    Regards,
    Rudi

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

    Re: Sort Data (office xp)

    It's basically a Select Sort on the last row, but only the even-numbered columns are taken into account.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Data (office xp)

    Hans, i can do your macro in my sample worksheet. but i cannot do the correct macro in my real worksheet. my A3 range is actually in AU1 or AT1 (that i can insert or delete just one column) and below my Total, (which is the last row you sort) has data and i want the data to be sort as well following to where the Total sorted.
    Can it be the range adjusted?? Attached is a sample task for your checking. my actual data to sort is from AU1 to CC51 and the Total is in row 23.

    Do you know what i am talking about?

    Thanks for your help.

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

    Re: Sort Data (office xp)

    Try this version:

    Sub SortSales()
    Dim i As Long
    Dim iMax As Long
    Dim j As Long
    Dim jMax As Long
    Dim MaxSale As Double
    Dim rng As Range
    Dim lngRows As Long
    ' *** Adjust as needed ***
    Const lngSortRow = 23
    Set rng = Range("AU1:CC51")
    ' ************************
    lngRows = rng.Rows.Count
    iMax = rng.Columns.Count
    For i = 2 To iMax - 3 Step 2
    MaxSale = rng.Cells(lngSortRow, i)
    jMax = i
    For j = i + 2 To iMax
    If rng.Cells(lngSortRow, j) > MaxSale Then
    MaxSale = rng.Cells(lngSortRow, j)
    jMax = j
    End If
    Next j
    If jMax > i Then
    rng.Cells(1, jMax).Resize(lngRows, 2).Cut
    rng.Cells(1, i).Resize(lngRows, 2).Insert Shift:=xlToRight
    End If
    Next i
    End Sub

    Change the constant lngSortRow as needed - it must indicate the row to sort on (the totals row) within the range.
    Change the definition of rng as needed - it must be the entire range to be sorted.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Data (office xp)

    Thank you Hans. This does the work. I would like to add one criteria to the macro: Any starting range have to start with 1 (e.g. AU1) although the range to sort may start at e.g. AU3.

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

    Re: Sort Data (office xp)

    I'm sorry, I don't understand. Can you try to explain that again?

  9. #9
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Data (office xp)

    Hans, in the attached file, the range to sort starts from A3 (where written YEAR label) to I15 and the Total row is at 9. If i put Set rng = Range("A3:I15"), the macro take no effect on the data and if i put Set rng = Range("A1:I15"), the data will sort according to the highest to the lowest Total figure.

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

    Re: Sort Data (office xp)

    You must use

    Const lngSortRow = 7
    Set rng = Range("A3:I15")

    The row to sort on is row 7 of the range to be sorted. It is row 9 in the spreadsheet, but since your sort range starts at row 3, row 9 of the spreadsheet is row 7 of the sort range.

  11. #11
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Data (office xp)

    I see. Thanks a lot Hans.

Posting Permissions

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