Sort Data (office xp)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

I see. Thanks a lot Hans.

Reply | Quote