# Sort Lottery Number Rows in Excel

One of the best features of Excel is that it’s quick and easy to sort columns of data.

You can even sort data in an Excel row, left to right, by changing one of the sort options.

### 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:

=SMALL(A1:F1,{1,2,3,4,5,6})

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, _
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.

_____________

### 14 Responses

1. Jim Cone says:

Debra,
It’s hard to go wrong with any code from Dave Peterson.
“Make a copy of your original worksheet, before sorting with the macro.” should be heeded.

The following very similar code to Dave’s, allows one to sort rows in the selection.
(instead of a fixed location)
Also, the code might run a little faster as it eliminates the use of the Resize property.
It sorts ascending unless one specifies a descending sort.
Do that by adding the number 2 or the expression “xlDescending” to the GetMeStarted sub…
This sorts ascending… Call SortRowsAcross
This sorts descending… Call SortRowsAcross(xlDescending)
This also sorts descending… Call SortRowsAcross(2)

‘–code starts–

```Sub GeMeStarted()
Call SortRowsAcross
End Sub

Function SortRowsAcross(Optional ByRef lngDirection As Long = 1)
Dim rngAll As Range
Dim rngRw As Range
Set rngAll = Selection
For Each rngRw In rngAll.Rows
rngRw.Sort key1:=rngRw, Order1:=lngDirection, header:=xlNo, _
MatchCase:=False, Orientation:=xlLeftToRight
Next ‘rngRw
End Function```

‘–code ends–

Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

2. Thanks Jim!

3. Jon Peltier says:

Critical note. When sorting, make sure you sort the entire block of data. If you sort only some columns of a range, you’ll never be able to piece those rows together again.

4. Sai says:

There is an error in the array formula SMALL(A1:F1,{1,2,3,4,5,6})
The correct formula is SMALL(A1:F1,{1;2;3;4;5;6}) and Ctrl+Shift+Enter

• Yes, if your regional settings use a semi-colon as the separator, you’ll have to change the formula to use those.

5. vicktor says:

still do not work. sorry somebody knows why.?

6. Larry says:

Just a small comment on the procedure of using a formula to sort, I tried following instruction of using =SMALL(A1:F1,{1,2,3,4,5,6})
selecting multiple cells and Ctrl+shit+enter. It does not work.

Here is what does work, select cell “H” then add formula “small” select the range(A1:F1), then return 1, go to cell “I” and add formula and range, using return 2, same for J(3),K(4),L(5)&M(6). Each cell of the 6 is done separately and each returning a different position from 1-6, ie: 1st, 2nd, 3rd etc. for smallest values.
Then it will return the proper values and sort the numbers in order.
After that then you can drag the formula down the column and it will sort each set(row)in order.
And as mentioned, to keep the values, use select all and copy and paste special values when done for the array.

• Corneliu says:

yes, this works perfectly

• Noel says:

Yes.. indeed.. First Cell,SMALL(A1:F1,{1;2;3;4;5;6}), Second Cell,SMALL(A1:F1,{2;3;4;5;6}) Third,SMALL(A1:F1,{3;4;5;6}) etc… worked perfectly for me.. Thanks a whole bunch..

7. Vince says:

I used the sort formula as written in Excel 2010 and it worked like a charm. Excellent post and thanks so much!

8. JEFF says:

Also, i am finding out the hard way that it will not sort using the formula if your array is stored in text format… i’m trying to find a way to convert all as numbers (it won’t let me by right button clicking and choose format) ie. even though it changes to ‘numbers’ format, it still won’t actually change to numbers… i’d have to click on each individual cell and actually convert each to numbers! grrrrr.

9. Francis says:

Hello.

Pls how can I sort list like this.

Item Unit Item Price
Pencil 2 Book \$100
Pen 1 Pen \$200
Book 3 Tape \$240
Eraser 8 Eraser \$50
Tape 1 Pencil \$500

Expected result

Item Unit
Pencil 2 \$100
Pen 1 \$200
Book 3 \$240
Eraser 8 \$50
Tape 1 \$500

Thanks.

10. Babulal Gandhi says:

Hi Debra
Your Row Sorting Formula Works Great.

Though I Have Numbers Like This To Sort.

22-34567, 333-7465, 876457689, 66-66970877 (A1,B1,C1,D1)

These Are Phone Numbers Which I Have To Short For Small To Large Or Large To Small

I Need Formula To Ignore (-)

Your Formula Works Only On Without (-) Numbers.

Thanks & Regards
Babulal Gandhi

11. Babulal Gandhi says:

I Have Phone Numbers Like This To Sort By Rows

66-66970877, 22-34567, 8764576890, 333-7465

(Column A1,Column B1,Column C1,Column D1)

These Are Phone Numbers Which I Have To Short For Small To Large Or Large To Small

Sorting Data In Row P1 To S1

22-34567, 333-7465, 66-66970877, 8764576890

(Column P1,Column Q1,Column R1,Column S1)

Column P1 = small(\$a1:\$d1,1),

Column Q1 = small(\$a1:\$d1,2),

Column R1 = small(\$a1:\$d1,3),

Column S1 = small(\$a1:\$d1,4)

These Formula Works Great On Without (-) Numbers.

I Need Formula To Ignore (-)

Numbers Before (-) Is Area Codes. If I Remove (-) With Find & Replace. I Will Get Confuse In Future As I Can’t Differentiate Area Code And Phone Number.

If There Is Solution In Macro

I Want To Short Telephone Numbers In Three Rows After Mobile Numbers In Other Three Rows.

P1-Telephone Number, Q1-Telephone Number, R1-Telephone Number, S1-Mobile Number, T1-Mobile Number, U1-Mobile Number

I Need A Macro

Ignores The (-) Value In All Phone Numbers

Check First Digit Of Phone Numbers.

If First Digit Start’s With Nine(9). If There Is Any Single Phone Number In A1, B1, C1, D1 Starting With Nine(9)(It’s Mobile Number) Put Into S1. Else If There’s Two Numbers Starting With Nine (9) Place It In Row S1 And T1 In Ascending Or Descending Order.

If First Digit Start’s With Eight (8). If There Is Any Single Phone Number In A1, B1, C1, D1 Starting With Eight (8) Put Into Q1. Else If There’s Two Numbers Starting With Eight (8) Place It In Row Q1 And R1 In Ascending Or Descending Order.

Like Wise For Other Digits Too

Thanks & Regards

Babulal Gandhi