Sorting rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have ten columns of numbers (i.e. F2:O2) with multiple rows. I need to sort the numbers in ascending order. I can do it using the drop down tabs, but I want to use a formula so I can apply it the all the rows. Can anyone help me with a formula to do this? Thanks for your time.

Tom
 
Hi Tom,
Not clear on your question. If you are trying to get around the
limitation of there being only three dropdowns for the sort,
you can sort the three most minor of the sort control, then
the next three higher etc, until you sort the most major
of the columns. You can record a macro when you do that.


tom330_6 said:
I have ten columns of numbers (i.e. F2:O2) with multiple rows. I need to sort the numbers in ascending order. I can do it using
Click to expand...
the drop down tabs, but I want to use a formula so I can apply it the all the rows. Can anyone help me with a formula to do this?
Thanks for your time.
 
I can do one row at a time with no problem. After new entries, I have to go back and to the drop down and do the process again. Not very efficient. I want to copy a "formula" to all the rows to keep from having to use the drop down one row at a time. If there are new entries (added rows). I would like it to sort using the "formula" already in an empty cell
Low High 1 2 3 4 5 6 7 8 9 1
180 198 180 188 191 192 192 195 195 195 197 19
174 196 174 195 194 192 194 194 193 194 192 19
178 193 189 188 191 178 193 191 190 188 186 18
In columns labeled 1-10 (in the middle of a spredsheet) I want to sort the numbers in assending order. The lowest number will be replaced with a higher number when one is achieved. After replaceing the lowest number, I need it to automatically resort.
I hope this gives you more info on my question
To
 
data-> sort -> options -> sort left to right




-----Original Message-----
I can do one row at a time with no problem. After new
Click to expand...
entries, I have to go back and to the drop down and do the
process again. Not very efficient. I want to copy
a "formula" to all the rows to keep from having to use the
drop down one row at a time. If there are new entries
(added rows). I would like it to sort using the "formula"
already in an empty cell.
Low High 1 2 3 4 5 6 7 8 9 10
180 198 180 188 191 192 192 195 195 195 197 198
174 196 174 195 194 192 194 194 193 194 192 196
178 193 189 188 191 178 193 191 190 188 186 181
In columns labeled 1-10 (in the middle of a spredsheet) I
Click to expand...
want to sort the numbers in assending order. The lowest
number will be replaced with a higher number when one is
achieved. After replaceing the lowest number, I need it
to automatically resort.
 
Hi Tom,
The sort Left to Right option will stick so next time you have
to check that you have the correct option, but you would see rows
instead of columns.

Each row is independent so you cannot sort the range
all at one time but must do one row at a time,
sorting the data in the columns of each row per row..

You will need a macro, if you need help with installing/using
a macro see my page Getting Started with Macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Function is not what you would use. A function cannot change
another cell. You might want to take a look at one of Chip
Pearson's page for the difference between a macro and a function.
Macros as Opposed to Functions (as found in topic.htm)
http://www.cpearson.com/excel/differen.htm

I had created a macro

Option Explicit
Sub Macro28()
'sort columns in each row within range B2:E28
Dim R As Long
For R = 2 To 28
Range("B" & R & ":E" & R).Sort key1:=Range("B" & R), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
Next R
End Sub

but then I looked at my webpage
http://www.mvps.org/dmcritchie/excel/sorting.htm
and saw that Tom Ogilvy had posted a better one but for columns:

Sub sortEachColumn()
'Tom Ogilvy, 2001-03-24, Programming
Dim col As Range
For Each col In Range("a2:g100").Columns
col.Sort key1:=col, Order1:=xlAscending
Next
End Sub

'-- so a modification to sort on each row instead of each column
'-- I also changed it to sort a selection rather than a specific range
'-- but you can change that to your requirement.

Sub sortEachRow()
'based on Tom Ogilvy, 2001-03-24, Programming
Dim rw As Range
If Selection.Columns.Count = 1 Then
MsgBox "your selection must involve more than one cell or column"
Exit Sub
End If
For Each rw In Selection.Rows
rw.Sort key1:=rw, Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
Next
End Sub
 
Thanks for your help. I have never used macros in excel before, but have in other programs. I will read throught your suggestions and play around with your examples. Thanks again.

Tom
 
Back
Top