Excel spreadsheet of Lotto results - formula question

Hi teckneeculler,

teckneeculler said:

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!

Hi teckneeculler,

teckneeculler said:

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!

teckneeculler said:

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

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!

Hi teckneeculler,

teckneeculler said:

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.

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!

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!