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.

excelsort01

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

excelsort16

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.

lotteryrowsort06

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

lotteryrowsort01

Then, copy the formula down to the last row of numbers, to see all the rows in ascending order.

lotteryrowsort02

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.

_____________

You may also like...

14 Responses

  1. Jim Cone says:

    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–

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

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

  4. vicktor says:

    still do not work. sorry somebody knows why.?

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

  6. Vince says:

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

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

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

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

    Could You Please Help Me

    Thanks & Regards
    Babulal Gandhi

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

Leave a Reply

Your email address will not be published. Required fields are marked *