Solved Excel spreadsheet of Lotto results - formula question

[teckneeculler]

I'm trying to query a spreadsheet of 100 Lotto draws.
I want to re-order all rows of 6 numbers from drawn order to low-high numeric order.
Here are a few typical ball sequences in the order the balls emerged:
a) 40-10-25-22-4-16
b) 34-17-7-39-31-30
c) 24-3-40-25-7-11

Instead, I'd like the sequences to look like this:
a) 4-10-16-22-25-40
b) 7-17-30-31-34-39
c) 3-7-11-24-25-40

Is this possible?
Thanks.

 

[Snick]

Sort data in Excel Rows
Columns are easier but you listed as rows
or
How to SORT in Excel (by Rows, Columns, Colors, Dates ...
Enjoy!
Bill

GCF_Learn_Free.org

 

[teckneeculler]

Thanks, but I didn't really want to learn Excel in depth - all I want to do is what I described above.

 

[Paul Black]

Hi teckneeculler,

I'm trying to query a spreadsheet of 100 Lotto draws.
I want to re-order all rows of 6 numbers from drawn order to low-high numeric order.
Here are a few typical ball sequences in the order the balls emerged:
a) 40-10-25-22-4-16
b) 34-17-7-39-31-30
c) 24-3-40-25-7-11

Instead, I'd like the sequences to look like this:
a) 4-10-16-22-25-40
b) 7-17-30-31-34-39
c) 3-7-11-24-25-40

Is this possible?

Yes.

It depends on how complex you want to make this!

The easiest solution is to perform Text-To-Colums to get rid of the "-" and have each number in its own cell.

Lets assume that your Lotto numbers are in cells A1:F?, put these formulas in cells H1:M1 and copy down . . .

=IF($A1=0,"",SMALL($A1:$F1,1))
=IF($A1=0,"",SMALL($A1:$F1,2))
=IF($A1=0,"",SMALL($A1:$F1,3))
=IF($A1=0,"",SMALL($A1:$F1,4))
=IF($A1=0,"",SMALL($A1:$F1,5))
=IF($A1=0,"",SMALL($A1:$F1,6))

I hope this helps!

 

[Paul Black]

Hi teckneeculler,

If you wanted the "-" between the numbers, you could put this formula in cell O1 and copy down . . .

=IF($A1=0,"",SMALL($A1:$F1,1)&"-"&SMALL($A1:$F1,2)&"-"&SMALL($A1:$F1,3)&"-"&SMALL($A1:$F1,4)&"-"&SMALL($A1:$F1,5)&"-"&SMALL($A1:$F1,6))

I hope this helps!

 

[teckneeculler]

Paul, thanks very much.
Re the dashes, there are none: the numbers are in cells, as in a regular spreadsheet.
I've attached a section of the spreadsheet.

 

[Paul Black]

Hi teckneeculler,

Re the dashes, there are none: the numbers are in cells, as in a regular spreadsheet.I've attached a section of the spreadsheet.

I have just looked at you spreadsheet and suggest you do the following . . .

In cells J2:O2 put numbers one to six in the respective cells.

In cell J3 enter . . .

=IF($C3=0,"",SMALL($C3:$H3,J$2))

Then copy this cell across to O3. Then copy cells J3:O3 down PAST the end of your data. The reason for this is it will accomodate future draws and actually show blanks because of the "" in the formulas.

I hope this helps!

 

[Snick]

you can do that with sort rows in excel
How To Sort Data In Rows In Excel
Sort a Row in Excel 2010

 

[Snick]

[FONT=&quot]Sorting a Row
Sorting in a Row.docx[/FONT]

[FONT=&quot]To sort by a row, follow these steps:[/FONT]

1. [FONT=&quot]Select one cell in the row you want to sort.[/FONT]
2. [FONT=&quot]Press Ctrl + A, to select the entire region. [/FONT]
3. [FONT=&quot]Check the selected area, to make sure that all the data is included. [/FONT]
4. [FONT=&quot]Right-click a cell in the row that you want to sort[/FONT]
5. [FONT=&quot]In the popup menu, click Sort, then click Custom Sort. [/FONT]

[FONT=&quot]

[/FONT]

1. [FONT=&quot]In the Sort dialog box, select the Day column in the Sort By box. [/FONT]
2. [FONT=&quot]From the Order drop down, select Custom List. [/FONT]
3. [FONT=&quot]At the top of the Sort dialog box, click Options. [/FONT]

[FONT=&quot]

[/FONT]

1. [FONT=&quot]In the Options dialog box, under Orientation, select Sort Left to Right. [/FONT]

1. [FONT=&quot]Click OK, to close the Options dialog box.[/FONT]
2. [FONT=&quot]From the Sort By drop down, select the row that you want to sort. There are no headings available, so select the correct Row number. [/FONT]

1. [FONT=&quot]Select the Sort On, and the Order options, then click OK.[/FONT]

[FONT=&quot]The data is sorted by the values in the selected row

Help? What you asked for? YES YES

Bill
[/FONT]

 

[teckneeculler]

Snick, thanks but I'm using Excel 2003. The GUI is quite different to later versions - much simpler, in fact, which I prefer.

 

[Paul Black]

Hi teckneeculler,

I'm using Excel 2003. The GUI is quite different to later versions - much simpler, in fact, which I prefer.

That's why I posted those formulas for you!

 

[teckneeculler]

LOL! Sorry, Paul, I've been so busy using your great formulas (while trying to work out for myself and not bother you about minor items I didn't understand), that I forgot to say Thank YOU!

My previous comment about Excel 2003 was just for Snick.

No, I've managed to do the 'copy down' - which puzzled me for a bit, but I've got it now - and then I started trying to sort the new six-column section - namely, sorting column J so that it's in numerical order, which, hopefully, will also reorder the data in the columns K, L, M, N, O.

This didn't work at first, but it seemed OK after I ran the 'Remove formulas and keep data' command on J to O columns.

I don't know if the original six columns C to H will follow suit, but it would be okay if they did. Or, I could delete them first and save the spreadsheet anew so it only contained the new six columns.

Your comments would be appreciated )

 

[Paul Black]

Hi teckneeculler,

LOL! Sorry, Paul, I've been so busy using your great formulas (while trying to work out for myself and not bother you about minor items I didn't understand), that I forgot to say Thank YOU!

You are very welcome!

. . . and then I started trying to sort the new six-column section - namely, sorting column J so that it's in numerical order, which, hopefully, will also reorder the data in the columns K, L, M, N, O.

This didn't work at first, but it seemed OK after I ran the 'Remove formulas and keep data' command on J to O columns.

I don't know if the original six columns C to H will follow suit, but it would be okay if they did. Or, I could delete them first and save the spreadsheet anew so it only contained the new six columns.

You really want to keep ALL your data intact on the same Worksheet.

[1] If you just SORT [re-order] column J, it will ONLY SORT column J and NO other columns. This will make ALL the data incorrect as far as EACH draw is concerned!

[2] There is NO need to DELETE ANY columns or rows.

To achieve what you are after . . .

Highlight cells A3:O? [where ? is your LAST row of data], and SORT on column J [Smallest to Largest]. This will keep ALL the data intact for EACH draw, it is just SORTED differently!

I hope this helps!

 

[Paul Black]

Hi teckneeculler,

If you wanted to put the draws put back to the way they ORIGINALLY were, then highlight cells A3:O? [where ? is your LAST row of data], and SORT on column A [Largest to Smallest], as per your upload.

I hope this helps!

 

[teckneeculler]

Paul, that's brilliant, much thanks! I assume there's no need to remove the formulas? I'll try it now

 

[Paul Black]

Hi teckneeculler,

Paul, that's brilliant, much thanks! I assume there's no need to remove the formulas? I'll try it now.

NO you DON'T need to REMOVE the FORMULAS.

 

[Paul Black]

Hi teckneeculler,

Getting the Spreadsheet back to normal is quite easy!

You just SORT column A back to Largest to Smallest.

I hope this helps!

 

[teckneeculler]

Oh, OK! Wondered about the formulas thing. I had tried again to do the sort and the same thing had happened as earlier - I could sort in the original columns but not the new ones.

Thanks for the tip about replacing the formulas. What's the easiest way to remove them in Excel 2007? I'm using this version on my laptop, thinking I should get progressive, but it's definitely overkill for my purposes

It's like Word from 2007-on. Can't stand the ribbon. I do a lot of writing but I only use Word 2003, and even that version I've heavily customized to be as simple as possible.

Cheers, Paul

 

[Paul Black]

Hi teckneeculler,

I thought that I would streamline this for you so it is pretty much automated. This way you will ONLY need to enter the NEW draws!

I have written four Macros for you to use. You just need to place them in a Module and then you can run whichever one you want at ANY time!

This code will enter the FORMULAS for ALL the draws. You MUST however, make sure that the numbers 1 to 6 are entered in cells J2:O2 . . .

Sub Enter_Formulas()
    With Worksheets("Winning Number Results")
        Range("J3:O" & Range("A" & Rows.Count).End(xlUp).Row).Formula = _
            "=IF($C3=0,"""",SMALL($C3:$H3,J$2))"
    End With
End Sub

This code will change the FORMULAS to VALUES, although I don't think that you will need to use this one . . .

Sub Convert_Formulas_To_Values()
    With Worksheets("Winning Number Results")
        Range("J3:O" & Range("A" & Rows.Count).End(xlUp).Row).Value = _
            Range("J3:O" & Range("A" & Rows.Count).End(xlUp).Row).Value
        
    End With
End Sub

This code will SORT on column J . . .

Sub Sort_Col_J()
Dim LastRow As Long
    With Worksheets("Winning Number Results")
        LastRow = Cells(Rows.Count, 2).End(xlUp).Row
        Range("A3:O" & LastRow).Sort key1:=Range("J3:J" & LastRow), _
            Order1:=xlAscending, Header:=xlNo
    End With
End Sub

This code will SORT on column A . . .

Sub Sort_Col_A()
Dim LastRow As Long
    With Worksheets("Winning Number Results")
        LastRow = Cells(Rows.Count, 2).End(xlUp).Row
        Range("A3:O" & LastRow).Sort key1:=Range("A3:A" & LastRow), _
            Order1:=xlDescending, Header:=xlNo
    End With
End Sub

I hope this helps!

 

[teckneeculler]

Paul, thank you so much, the macros are amazing! I assume they're something like a Word macro, only I won't need to create them, just open and enter the commands you've provided?

Your help has been invaluable!
LOL, gonna start tinkering right away