1. 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. 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. 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!

4. 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. 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?

6. 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. 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. Re: Sort Data (office xp)

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

9. 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. 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. 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
•