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

_____________

Debra,

It’s hard to go wrong with any code from Dave Peterson.

Also, your advice to:

“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–

‘–code ends–

Jim Cone

Portland, Oregon USA

http://www.mediafire.com/PrimitiveSoftware

Thanks Jim!

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.

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.

still do not work. sorry somebody knows why.?

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.

yes, this works perfectly

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

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

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.

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.

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.

Could You Please Help Me

Thanks & Regards

Babulal Gandhi

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