Results 1 to 11 of 11
Thread: Sort Data (office xp)

20050721, 05:33 #1
 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.

20050721, 09:52 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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

20050721, 14:04 #3
 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

20050721, 14:39 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Sort Data (office xp)
It's basically a Select Sort on the last row, but only the evennumbered columns are taken into account.

20050722, 01:54 #5
 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.

20050722, 05:53 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20050723, 05:58 #7
 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.

20050723, 07:41 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Sort Data (office xp)
I'm sorry, I don't understand. Can you try to explain that again?

20050725, 03:28 #9
 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.

20050725, 06:26 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20050725, 08:46 #11
 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.