
08 Nov 2019  #11 


My System Specs 
. 

09 Nov 2019  #12 

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 sixcolumn 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 Specs 
09 Nov 2019  #13 

Hi teckneeculler,
. . . and then I started trying to sort the new sixcolumn 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. [1] If you just SORT [reorder] 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 Specs 
. 

09 Nov 2019  #14 

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 Specs 
09 Nov 2019  #15 

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

My System Specs 
09 Nov 2019  #16 


My System Specs 
09 Nov 2019  #17 

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 Specs 
. 
09 Nov 2019  #18 

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 2007on. 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 Specs 
10 Nov 2019  #19 

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 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 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 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 
My System Specs 
10 Nov 2019  #20 

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 Specs 
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 informationthe 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 09:20. 
