Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.

# Windows 7: Excel spreadsheet of Lotto results - formula question

 03 Nov 2019 #1 teckneeculler Win7Ultimate 310 posts Excel spreadsheet of Lotto results - formula question 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. My System Specs
 .
 03 Nov 2019 #2 Snick Win 10 x64, Linux Lite, Win 7 x64, BlackArch, & Kali 2,420 posts 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 My System Specs
 03 Nov 2019 #3 teckneeculler Win7Ultimate 310 posts Thanks, but I didn't really want to learn Excel in depth - all I want to do is what I described above. My System Specs
 .
 04 Nov 2019 #4 Paul Black Win 7 HP SP1 64-bit Vista HB SP2 32-bit Linux Mint 18.3 6,168 posts Planet Earth VGhlIFgtRmlsZXM= Hi teckneeculler,    Quote: Originally Posted by 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 . . . 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! My System Specs
 04 Nov 2019 #5 Paul Black Win 7 HP SP1 64-bit Vista HB SP2 32-bit Linux Mint 18.3 6,168 posts Planet Earth VGhlIFgtRmlsZXM= 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! My System Specs
06 Nov 2019   #6
teckneeculler

Win7Ultimate

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.

Attached Files
 Lotto History exper.xlsx (70.6 KB, 4 views)
My System Specs
 06 Nov 2019 #7 Paul Black Win 7 HP SP1 64-bit Vista HB SP2 32-bit Linux Mint 18.3 6,168 posts Planet Earth VGhlIFgtRmlsZXM= Hi teckneeculler,    Quote: Originally Posted by 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 . . . 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! My System Specs
 .
 06 Nov 2019 #8 Snick Win 10 x64, Linux Lite, Win 7 x64, BlackArch, & Kali 2,420 posts you can do that with sort rows in excel How To Sort Data In Rows In Excel Sort a Row in Excel 2010 My System Specs
 06 Nov 2019 #9 Snick Win 10 x64, Linux Lite, Win 7 x64, BlackArch, & Kali 2,420 posts Sorting a Row Sorting in a Row.docx To sort by a row, follow these steps: Select one cell in the row you want to sort. Press Ctrl + A, to select the entire region. Check the selected area, to make sure that all the data is included. Right-click a cell in the row that you want to sort In the popup menu, click Sort, then click Custom Sort. [IMG]file:///C:\Users\Benjamin\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png[/IMG] In the Sort dialog box, select the Day column in the Sort By box. From the Order drop down, select Custom List. At the top of the Sort dialog box, click Options. [IMG]file:///C:\Users\Benjamin\AppData\Local\Temp\msohtmlclip1\01\clip_image002.png[/IMG] In the Options dialog box, under Orientation, select Sort Left to Right. Click OK, to close the Options dialog box. 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. 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 My System Specs
 08 Nov 2019 #10 teckneeculler Win7Ultimate 310 posts Snick, thanks but I'm using Excel 2003. The GUI is quite different to later versions - much simpler, in fact, which I prefer. My System Specs