Windows 7 Forums
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

4 Weeks Ago   #1
teckneeculler

Win7Ultimate
 
 
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 SpecsSystem Spec
.
4 Weeks Ago   #2
Snick

Win 10 x64, Linux Lite, Win 7 x64, BlackArch, & Kali
 
 

My System SpecsSystem Spec
4 Weeks Ago   #3
teckneeculler

Win7Ultimate
 
 

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

4 Weeks Ago   #4
Paul Black

Win 7 HP SP1 64-bit Vista HB SP2 32-bit Linux Mint 18.3
 
 

Hi teckneeculler,

Quote   Quote: Originally Posted by teckneeculler View Post
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 SpecsSystem Spec
4 Weeks Ago   #5
Paul Black

Win 7 HP SP1 64-bit Vista HB SP2 32-bit Linux Mint 18.3
 
 

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 SpecsSystem Spec
4 Weeks Ago   #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
File Type: xlsx Lotto History exper.xlsx (70.6 KB, 4 views)
My System SpecsSystem Spec
4 Weeks Ago   #7
Paul Black

Win 7 HP SP1 64-bit Vista HB SP2 32-bit Linux Mint 18.3
 
 

Hi teckneeculler,

Quote   Quote: Originally Posted by teckneeculler View Post
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 SpecsSystem Spec
.
4 Weeks Ago   #8
Snick

Win 10 x64, Linux Lite, Win 7 x64, BlackArch, & Kali
 
 

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 SpecsSystem Spec
4 Weeks Ago   #9
Snick

Win 10 x64, Linux Lite, Win 7 x64, BlackArch, & Kali
 
 

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
My System SpecsSystem Spec
4 Weeks Ago   #10
teckneeculler

Win7Ultimate
 
 

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 SpecsSystem Spec
Reply

 Excel spreadsheet of Lotto results - formula question




Thread Tools




Similar help and support threads
Thread Forum
Excel 2013 - how to keep a formula consisten
Hi I am trying to tie one cell into a formula that is being pasted into multiple rows. I know you can use $ to keep the cell consistent but it doesn't seem to be working. So if the value is in c9 and then first row gets c9 then the new row gets c10 etc... I need it to be c9 throughout the...
Microsoft Office
Excel Formula
Hello Sir, Can you let me know the formula in Excel: I have a table as below: This table, At the column "A,Status" when i Put the word" Completed", it's will automatic put current Date to Column "B, Row 2". But i don't know after that when tomorrow arrived,at the column "B,Row 2",...
Microsoft Office
Problem with Excel Spreadsheet
I have to admit that I am not proficient in the MS Excel program. I have to fill in a form for a potential employer but the cell is too small to contain all of the information--the information that I typed in goes "off the page" into neighboring cells that are not part of the form. I can't seem to...
Microsoft Office
Excel 2003 sum formula problem
I have been entering currency data in Column B in Euros to an EXCEL 2003 s/s and the formula used has added these totals entered in that Column correctly until I reached a total sum of 74364.70 Euros ,this being the sum of entries in Rows B6 to B82 . This was reached with the formula showing as...
Microsoft Office
Excel Formula Help please...
This is probably very simple and I have got it working but it isn't working how I want it to work. I have created an Excel document to show my monthly payments of a bank loan. I have created 3 columns in the first column is the payment number, in the second column is the amount that the loan...
Microsoft Office


Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 11:55.
Twitter Facebook