Welcome Guest
Log In | Register )
You last visited December 3, 2016, 1:41 am
All times shown are
Eastern Time (GMT-5:00)

Excel Question re: Macros

Topic closed. 23 replies. Last post 4 years ago by Elizabeth03.

Page 1 of 2
PrintE-mailLink
butch2030's avatar - DiscoBallGlowing
The KEY ingredient is Combos & Patterns
Elgin, IL
United States
Member #68867
January 1, 2009
1221 Posts
Offline
Posted: July 17, 2012, 10:29 pm - IP Logged

I am trying to put an out of order list of Lottery drawings in numberic order vs order drawn. 

such as 6 3 2 4 5 1 with each number in a different cell - Output should look like 1 2 3 4 5 6 each in it own cell.

In excel, you can highlight the cells  c6 d6 e6 f6 g6 h6 - go to Sort & Filter; Custom Sort; Options: Sort Left to Right;  Row 6 will appear & you will sort it smallest to largest.

I am trying to create a macro so I do not have to do this on an individual line - one at a time. Have it sort one line - drop down 1 line  & do it all over again in a loop.

Can anyone help?

Macros are not my expertise.

Thanking you in advance.

    time*treat's avatar - radar

    United States
    Member #13130
    March 30, 2005
    2171 Posts
    Offline
    Posted: July 17, 2012, 10:47 pm - IP Logged

    If you know how to edit a macro, do a search for bubble sort macro. Make the needed changes.

    In neo-conned Amerika, bank robs you.
    Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

      butch2030's avatar - DiscoBallGlowing
      The KEY ingredient is Combos & Patterns
      Elgin, IL
      United States
      Member #68867
      January 1, 2009
      1221 Posts
      Offline
      Posted: July 17, 2012, 11:19 pm - IP Logged

      If you know how to edit a macro, do a search for bubble sort macro. Make the needed changes.

      Like I said it is not my expertise.  I think I wrote one macro about 15 years ago.  That was my extent with macros.

        butch2030's avatar - DiscoBallGlowing
        The KEY ingredient is Combos & Patterns
        Elgin, IL
        United States
        Member #68867
        January 1, 2009
        1221 Posts
        Offline
        Posted: July 17, 2012, 11:48 pm - IP Logged

        Anyone else????

          Avatar
          New Member

          United States
          Member #97137
          September 11, 2010
          2 Posts
          Offline
          Posted: July 17, 2012, 11:57 pm - IP Logged

          I really did not understand what you said. I wanted to help you. Can you give me any clear idea than this.

          You will achive.

          thanks.

            butch2030's avatar - DiscoBallGlowing
            The KEY ingredient is Combos & Patterns
            Elgin, IL
            United States
            Member #68867
            January 1, 2009
            1221 Posts
            Offline
            Posted: July 18, 2012, 8:26 am - IP Logged

            I really did not understand what you said. I wanted to help you. Can you give me any clear idea than this.

            You will achive.

            thanks.

            I am trying to convert a listing of actual lottery drawings to a listing of drawings which are arranged smallest to largest numbers: For example:

            Listing Before:

            23-14-10-36-2-46

            8-9-16-3-42-13

            Listing After:

            2-10-14-23-36-46

            3-8-9-13-16-42

            Instead of doing it manually, I am trying to convert them by using a macro.  I know what a Macro does in excel, my problem is I don't know how to write a Macro.

              Epistrophy's avatar - Lottery-042.jpg
              San Diego, CA
              United States
              Member #112369
              June 17, 2011
              187 Posts
              Offline
              Posted: July 18, 2012, 12:47 pm - IP Logged

              I don't know whether this will help you, but I just tested it and you can select multiple rows and columns and still have it sort left to right.

              That way, you don't need to write a macro because it can sort all the rows at the same time.

              So if you select, for example, from A1 to E99 (or whatever it is on your spreadsheet) then choose Data-Sort-Options you can do the left-to-right sort all at once.

              Hope this helps, and good luck.

                butch2030's avatar - DiscoBallGlowing
                The KEY ingredient is Combos & Patterns
                Elgin, IL
                United States
                Member #68867
                January 1, 2009
                1221 Posts
                Offline
                Posted: July 18, 2012, 1:44 pm - IP Logged

                I don't know whether this will help you, but I just tested it and you can select multiple rows and columns and still have it sort left to right.

                That way, you don't need to write a macro because it can sort all the rows at the same time.

                So if you select, for example, from A1 to E99 (or whatever it is on your spreadsheet) then choose Data-Sort-Options you can do the left-to-right sort all at once.

                Hope this helps, and good luck.

                Thanks,

                I know this as I was doing it  for the first couple of rows.  But I have to select over 500 rows to do it all at once.  It would have been alot easier to have a macro do it.

                I guess I will will do it in batches of 25 rows at a time.

                Thanks for your info though.

                  time*treat's avatar - radar

                  United States
                  Member #13130
                  March 30, 2005
                  2171 Posts
                  Offline
                  Posted: July 18, 2012, 7:55 pm - IP Logged

                  Like I said it is not my expertise.  I think I wrote one macro about 15 years ago.  That was my extent with macros.

                  I try to write them so it is easy enough to figure out what to change if you want to alter your starting point, range, or worksheet name, etc.

                  Caveat: This simple version looks at Sheet1, cols 3 through 8, starting at row 6 and doesn't check for duplicates or letters.

                   

                  Option Explicit

                  Sub simple__sort()
                   
                    'simple sorting macro'
                    ' by time*treat '
                   
                    Dim row As Long, col As Long
                    Dim temp As Integer, offset As Integer
                    Dim first As Integer, last As Integer
                    Dim init() As Integer, group__size As Integer
                   
                    row = 6: offset = 3: group__size = 6
                    ReDim init(group__size)
                   
                    While Sheets("Sheet1").Cells(row, offset).Value <> ""
                      For col = offset To offset + group__size - 1
                        init(col - 2) = Sheets("Sheet1").Cells(row, col).Value
                      Next col
                     
                      For first = 1 To group__size - 1
                        For last = first + 1 To group__size
                          If init(first) > init(last) Then
                            temp = init(first)
                            init(first) = init(last)
                            init(last) = temp
                          End If
                        Next last
                      Next first
                     
                      For col = offset To offset + group__size - 1
                        Sheets("Sheet1").Cells(row, col).Value = init(col - 2)
                      Next col
                      row = row + 1
                    Wend

                  End Sub

                  In neo-conned Amerika, bank robs you.
                  Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

                    Epistrophy's avatar - Lottery-042.jpg
                    San Diego, CA
                    United States
                    Member #112369
                    June 17, 2011
                    187 Posts
                    Offline
                    Posted: July 18, 2012, 8:00 pm - IP Logged

                    Time*treat, that's some nice compact code right there.  Elegant is the word that comes to mind.

                      butch2030's avatar - DiscoBallGlowing
                      The KEY ingredient is Combos & Patterns
                      Elgin, IL
                      United States
                      Member #68867
                      January 1, 2009
                      1221 Posts
                      Offline
                      Posted: July 18, 2012, 11:27 pm - IP Logged

                      I try to write them so it is easy enough to figure out what to change if you want to alter your starting point, range, or worksheet name, etc.

                      Caveat: This simple version looks at Sheet1, cols 3 through 8, starting at row 6 and doesn't check for duplicates or letters.

                       

                      Option Explicit

                      Sub simple__sort()
                       
                        'simple sorting macro'
                        ' by time*treat '
                       
                        Dim row As Long, col As Long
                        Dim temp As Integer, offset As Integer
                        Dim first As Integer, last As Integer
                        Dim init() As Integer, group__size As Integer
                       
                        row = 6: offset = 3: group__size = 6
                        ReDim init(group__size)
                       
                        While Sheets("Sheet1").Cells(row, offset).Value <> ""
                          For col = offset To offset + group__size - 1
                            init(col - 2) = Sheets("Sheet1").Cells(row, col).Value
                          Next col
                         
                          For first = 1 To group__size - 1
                            For last = first + 1 To group__size
                              If init(first) > init(last) Then
                                temp = init(first)
                                init(first) = init(last)
                                init(last) = temp
                              End If
                            Next last
                          Next first
                         
                          For col = offset To offset + group__size - 1
                            Sheets("Sheet1").Cells(row, col).Value = init(col - 2)
                          Next col
                          row = row + 1
                        Wend

                      End Sub

                      Thank you ever so much Time*Treat - It worked like a charm..........................................

                      I owe you one for this...................

                      Thanks again for everything

                      Good Luck & Best Wishes To You.

                        SergeM's avatar - slow icon.png
                        Economy class
                        Belgium
                        Member #123700
                        February 27, 2012
                        4035 Posts
                        Offline
                        Posted: July 19, 2012, 7:50 am - IP Logged

                        I am trying to convert a listing of actual lottery drawings to a listing of drawings which are arranged smallest to largest numbers: For example:

                        Listing Before:

                        23-14-10-36-2-46

                        8-9-16-3-42-13

                        Listing After:

                        2-10-14-23-36-46

                        3-8-9-13-16-42

                        Instead of doing it manually, I am trying to convert them by using a macro.  I know what a Macro does in excel, my problem is I don't know how to write a Macro.

                        Instead of doing it manually, I am trying to convert them by using a macro.

                        There is a macro recorder! I suggest that you use it.

                          Elizabeth03's avatar - cat anm.gif
                          Nova Scotia
                          Canada
                          Member #9934
                          December 27, 2004
                          884 Posts
                          Offline
                          Posted: July 19, 2012, 4:42 pm - IP Logged

                          butch2030, I messaged you prior, also how many combo's do you play per draw?

                            butch2030's avatar - DiscoBallGlowing
                            The KEY ingredient is Combos & Patterns
                            Elgin, IL
                            United States
                            Member #68867
                            January 1, 2009
                            1221 Posts
                            Offline
                            Posted: July 19, 2012, 5:12 pm - IP Logged

                            Depends on the game & the amount of the jackpot.  I usually only play 2 cards on the IL Lotto, 2 cards on the Megaball, & 2 cards on the Powerball.  The IL Lotto is the best buy for me as I get 10 lines per card @ .50/line - one card is $5.00.  I do play the combinations & patterns that I have found in these games.

                              butch2030's avatar - DiscoBallGlowing
                              The KEY ingredient is Combos & Patterns
                              Elgin, IL
                              United States
                              Member #68867
                              January 1, 2009
                              1221 Posts
                              Offline
                              Posted: July 19, 2012, 5:20 pm - IP Logged

                              Depends on the game & the amount of the jackpot.  I usually only play 2 cards on the IL Lotto, 2 cards on the Megaball, & 2 cards on the Powerball.  The IL Lotto is the best buy for me as I get 10 lines per card @ .50/line - one card is $5.00.  I do play the combinations & patterns that I have found in these games.

                              I do not play other state or country lotteries.  I do look for combination and patterns in other games.  I hope that by doing this - Others will realize that each game has it own combination & patterns and will improve their chances of winning.  You cannot change the Odds of Winning, but you can improve your Chances of Winning.

                              I just finished doing my combos & patterns with the Philippine Lotto 6/42 game.  I had download their history of the game from 01/03/09 to present.  I did run into a problem as their history had the winning numbers as they were drawn ( not in any order).  With the help from time*treat and the macro he provided, I was able to complete my file on the Philippine lotto 6/42 game.  The results like all the others are posted on my blog.  I do not play this game nor do I intend to.  I just want to provided players that do with something that might give them better chances of winning.