Excel spreadsheet of Lotto results - formula question

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

Hi teckneeculler,

teckneeculler said:

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

Code:

=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!

Hi teckneeculler,

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

Code:

=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!

Hi teckneeculler,

teckneeculler said:

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

Code:

=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!

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

Sorting a Row
Sorting in a Row.docx

To sort by a row, follow these steps:

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

[IMG]file:///C:\Users\Benjamin\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png[/IMG]

1. In the Sort dialog box, select the Day column in the Sort By box.
2. From the Order drop down, select Custom List.
3. At the top of the Sort dialog box, click Options.

[IMG]file:///C:\Users\Benjamin\AppData\Local\Temp\msohtmlclip1\01\clip_image002.png[/IMG]

1. In the Options dialog box, under Orientation, select Sort Left to Right.

1. Click OK, to close the Options dialog box.
2. 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.

1. Select the Sort On, and the Order options, then click OK.

The data is sorted by the values in the selected row

Help? What you asked for? YES YES

Bill