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 Days Ago   #11
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 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!


My System SpecsSystem Spec
.
3 Days Ago   #12
teckneeculler

Win7Ultimate
 
 

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 8-))
My System SpecsSystem Spec
3 Days Ago   #13
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
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!

Quote   Quote: Originally Posted by teckneeculler View Post
. . . 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!
My System SpecsSystem Spec
.

3 Days Ago   #14
Paul Black

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

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!
My System SpecsSystem Spec
3 Days Ago   #15
teckneeculler

Win7Ultimate
 
 

Paul, that's brilliant, much thanks! I assume there's no need to remove the formulas? I'll try it now
My System SpecsSystem Spec
3 Days Ago   #16
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
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.
My System SpecsSystem Spec
3 Days Ago   #17
Paul Black

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

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!
My System SpecsSystem Spec
.
3 Days Ago   #18
teckneeculler

Win7Ultimate
 
 

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
My System SpecsSystem Spec
2 Days Ago   #19
Paul Black

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

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

Code:
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 . . .
Code:
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 . . .
Code:
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 . . .
Code:
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!
My System SpecsSystem Spec
2 Days Ago   #20
teckneeculler

Win7Ultimate
 
 

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
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 02:53.
Twitter Facebook