Sort Multiple Rows of Data With a Formula
In a comment on the Sort a Row in Excel 2010 blog post, Debbie asked about sorting 2000 rows, left to right. She didn’t say they were lottery numbers, but her example, shown below, sure looks like that to me.
One way to sort the rows is to use a formula, in columns to the right.
In the screen shot below, cells H1:M1 are selected, and this SMALL formula is entered:
Then, to array-enter the formula, press Ctrl+Shift+Enter
Then, copy the formula down to the last row of numbers, to see all the rows in ascending order.
As a final step, you could copy the columns of formulas, and paste them as values.
Sort Multiple Rows with a Macro
If you don’t want to mess with formulas, you could use a macro to sort each row, left to right. This macro, from Dave Peterson, will sort all the rows on the active sheet, starting in row 1, and assumes there are 6 columns of numbers.
Make a copy of your original worksheet, before sorting with the macro.
Sub SortLotteryRows() 'posted by Dave Peterson Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long With ActiveSheet FirstRow = 1 'change to 2 if there are headings LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = FirstRow To LastRow With .Cells(iRow, "A").Resize(1, 6) .Sort Key1:=.Columns(1), _ Order1:=xlAscending, _ Header:=xlNo, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlLeftToRight End With Next iRow End With End Sub
Other Row Sorting Ideas
Do you have any other ideas for sorting lots of rows? Please share your ideas in the comments.