Welcome Guest
Log In | Register )
You last visited January 21, 2017, 5:35 am
All times shown are
Eastern Time (GMT-5:00)

Excel Question

Topic closed. 32 replies. Last post 7 years ago by LottoChica23.

Page 1 of 3
PrintE-mailLink
Play4Keeps's avatar - 656f0b5f5f1f6b58
End Of The Rainbow
United States
Member #67807
December 5, 2008
489 Posts
Offline
Posted: November 29, 2009, 12:10 am - IP Logged

Is there a way that I can download the pick 3, pick 4, cash 5, or mega millions in excel format. I am trying to learn how to make my own excel spreadsheets, but, when I download the numbers from the Virginia Lottery website they come in Word.

I tried to change the download file type to .xls, but, then it is just a jumbled up mess. All of the data is compressed into a couple of cells.

What I wanted to do was to try to get the numbers into excel and then 'make' a pivot table. Something I saw on Mr. Excel in YouTube.

Any help would be greatly appreciated.

 

Thanks,

Sandra

In Everything You Do.....PLAY 4 KEEPS!

Lots of luck, everyone!!

         





    Play4Keeps's avatar - 656f0b5f5f1f6b58
    End Of The Rainbow
    United States
    Member #67807
    December 5, 2008
    489 Posts
    Offline
    Posted: November 29, 2009, 12:29 am - IP Logged

    What my goal is, is this. Thinking of...I would like to be able to set up an excel program that would let me look at the same month back through history....say November.....and see what numbers favor this month.

    I hope that I'm explaining this correctly.

    And, I would like to be able to have a 'bell curve' or some kind of graph on the program to show number frequency....

     

    and....what number likes to pair up with what number.

     

    I know it's a lot....Can this be done in excel?

     

    DIAMOND....WHERE ARE YOU, WHEN A GIRL NEEDS YOU!!!Big Grin

    In Everything You Do.....PLAY 4 KEEPS!

    Lots of luck, everyone!!

             





      LANTERN's avatar - kilroy 28_173_reasonably_small.jpg
      Tx
      United States
      Member #4570
      May 4, 2004
      5180 Posts
      Offline
      Posted: November 29, 2009, 3:29 am - IP Logged

      Is there a way that I can download the pick 3, pick 4, cash 5, or mega millions in excel format. I am trying to learn how to make my own excel spreadsheets, but, when I download the numbers from the Virginia Lottery website they come in Word.

      I tried to change the download file type to .xls, but, then it is just a jumbled up mess. All of the data is compressed into a couple of cells.

      What I wanted to do was to try to get the numbers into excel and then 'make' a pivot table. Something I saw on Mr. Excel in YouTube.

      Any help would be greatly appreciated.

       

      Thanks,

      Sandra

      The numbers are in a .doc file, you need them to be in a comma delimited file to load them into Excel.

      There might be some other way, I don't know anything abut Excel. maybe you can either import them into Excel and or copy and then paste them on-to it.

      BibleOnline  ParishesOnline  ChristianRadioOnline   MassOnline   Mass

      "Ten measures of beauty descended to the world, nine were taken by Jerusalem."

        LANTERN's avatar - kilroy 28_173_reasonably_small.jpg
        Tx
        United States
        Member #4570
        May 4, 2004
        5180 Posts
        Offline
        Posted: November 29, 2009, 3:39 am - IP Logged

        The numbers are in a .doc file, you need them to be in a comma delimited file to load them into Excel.

        There might be some other way, I don't know anything abut Excel. maybe you can either import them into Excel and or copy and then paste them on-to it.

        it was very easy to convert them in such a way that they would load like this:

        You only need to use the search and replace function and then to change DOC to CSV.

        Change the separators or whatever their name to ,

        Easy that is all:

        See?                                                                                                                                                                                                                                                     

        5-22-1989, Night, 9,1,4
        5-23-1989, Night, 5,2,2
        5-24-1989, Night, 2,3,9
        5-25-1989, Night, 7,4,5
        5-26-1989, Night, 9,6,7
        5-27-1989, Night, 7,0,1
        5-29-1989, Night, 6,5,9
        5-30-1989, Night, 1,8,9
        5-31-1989, Night, 2,9,3
        6-1-1989, Night, 0,6,0
        6-2-1989, Night, 6,5,4
        6-3-1989, Night, 0,8,5
        6-5-1989, Night, 5,5,6
        6-6-1989, Night, 0,2,9
        6-7-1989, Night, 3,7,4
        6-8-1989, Night, 5,5,0
        6-9-1989, Night, 9,3,0
        6-10-1989, Night, 0,7,9
        6-12-1989, Night, 7,9,6
        6-13-1989, Night, 6,8,6

        BibleOnline  ParishesOnline  ChristianRadioOnline   MassOnline   Mass

        "Ten measures of beauty descended to the world, nine were taken by Jerusalem."

          Play4Keeps's avatar - 656f0b5f5f1f6b58
          End Of The Rainbow
          United States
          Member #67807
          December 5, 2008
          489 Posts
          Offline
          Posted: November 29, 2009, 9:30 am - IP Logged

          Good Morning, Lantern!

          Thanks for replying Smile. I am squeaky new to excel, but, I really am trying to understand it by baby steps. Just as soon as I can get past the 'deer in the headlights' look, I'll be o.k.

          The 'search and replace' function....do you have time to type out how to do that?

           

          Many Thanks,

          Sandra

          In Everything You Do.....PLAY 4 KEEPS!

          Lots of luck, everyone!!

                   





            Play4Keeps's avatar - 656f0b5f5f1f6b58
            End Of The Rainbow
            United States
            Member #67807
            December 5, 2008
            489 Posts
            Offline
            Posted: November 29, 2009, 9:40 am - IP Logged

            Ok. I'm playing around with it and I clicked on 'Edit'.

            Then, I clicked on 'Find and Replace' which brought up this box...

            Dang...I don't know how to insert an image of the excel screen here like you did.Unhappy

            This is in the 'Help' box that came up about the 'Find and Replace'....

            Find or replace text and numbers on a worksheet

            Show AllShow All
            Hide AllHide All
            1. Select the range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells that you want to search.

              If you want to search the entire worksheet, click any cell.

            2. On the Edit menu, click one of the following:
              • To find text or numbers, click Find.
              • To find and replace text, click Replace.
            3. In the Find what box, type the text or numbers that you want to search for, or click the arrow in the Find what box, and then click a recent search in the list.

              You can use wildcard characters such as an asterisk (*) or a question mark (?) in your search criteria:

              • Use the asterisk to find any string of characters. For example, s*d finds "sad" and "started".
              • Use the question mark to find any single character. For example, s?t finds "sat" and "set".

              Note  You can find asterisks, question marks, and tilde characters (~) in worksheet data by preceding them with a tilde character in the Find what box. For example, to find data that contains "?", you would type ~? as your search criteria.

            4. Click Options to further define your search, and then do any of the following:
              • In the Within box, select Sheet or Workbook to search a worksheet or an entire workbook.
              • In the Search box, click By Rows or By Columns.
              • In the Look in box, click Formulas, Values, or Comments.
              • To search for case-sensitive content, select the Match case check box.
              • To search for cells that contain just the characters that you typed in the Find what box, select the Match entire cell contents check box.
            5. If you want to search for text or numbers that also have specific formatting, click Format and make your selections in the Find Format dialog box.

              Tip   If you want to find cells that just match a specific format, you can delete any criteria in the Find what box and then select a specific cell format as an example. Click the arrow next to Format, click Choose Format From Cell, and then click the cell that has the formatting that you want to search for.

            6. Do one of the following:
              • To find text or numbers, click Find All or Find Next.

                Tip   When you click Find All, every occurrence of the criteria that you are searching for will be listed, and you can make a cell active by clicking a specific occurrence in the list. You can sort the results of a Find All search by clicking a column heading.

              • To replace text or numbers, type the replacement characters in the Replace with box (or leave this box blank to replace the characters with nothing), and then click Find or Find All.

                Note  If the Replace with box is not available, click the Replace tab.

              Tip   If needed, you can cancel a search in progress by pressing ESC.

            7. To replace the highlighted occurrence or all occurrences of the found characters, click Replace or Replace All.

             

            Am I at the correct place?

             

            Many Thanks,

            Sandra

            In Everything You Do.....PLAY 4 KEEPS!

            Lots of luck, everyone!!

                     





              Play4Keeps's avatar - 656f0b5f5f1f6b58
              End Of The Rainbow
              United States
              Member #67807
              December 5, 2008
              489 Posts
              Offline
              Posted: November 29, 2009, 9:58 am - IP Logged

              After I clicked on 'Edit' and the 'Find and Replace' box comes up....it has two blank areas in which to type info.

              Find: ____________________

              Replace: _________________

              When I type in 'Pick3_ASCII.doc' in the 'Find' area and 'Pick3_ASCII.csv' in the replace area, another box comes up and indicates that Excel cannot find what I've typed in.

               

              Still trying....

              In Everything You Do.....PLAY 4 KEEPS!

              Lots of luck, everyone!!

                       





                LANTERN's avatar - kilroy 28_173_reasonably_small.jpg
                Tx
                United States
                Member #4570
                May 4, 2004
                5180 Posts
                Offline
                Posted: November 29, 2009, 10:01 am - IP Logged

                Sandra

                Your past draws are given to you as a Doc file. open them with either NotePad or with WordPad

                Once they are opened with either of those go to:

                If with WordPad

                Edit, then go to

                Replace

                More in a moment

                BibleOnline  ParishesOnline  ChristianRadioOnline   MassOnline   Mass

                "Ten measures of beauty descended to the world, nine were taken by Jerusalem."

                  LANTERN's avatar - kilroy 28_173_reasonably_small.jpg
                  Tx
                  United States
                  Member #4570
                  May 4, 2004
                  5180 Posts
                  Offline
                  Posted: November 29, 2009, 10:02 am - IP Logged

                  Just wait a moment O.K.?

                  BibleOnline  ParishesOnline  ChristianRadioOnline   MassOnline   Mass

                  "Ten measures of beauty descended to the world, nine were taken by Jerusalem."

                    LANTERN's avatar - kilroy 28_173_reasonably_small.jpg
                    Tx
                    United States
                    Member #4570
                    May 4, 2004
                    5180 Posts
                    Offline
                    Posted: November 29, 2009, 10:07 am - IP Logged

                    Your number look like this after you first open them up with WordPad or with NotePad:

                    Results for Pick 3                                                                                                                                                                                                                                                         

                    5-22-1989; Night: 9,1,4
                    5-23-1989; Night: 5,2,2
                    5-24-1989; Night: 2,3,9
                    5-25-1989; Night: 7,4,5
                    5-26-1989; Night: 9,6,7
                    5-27-1989; Night: 7,0,1
                    5-29-1989; Night: 6,5,9
                    5-30-1989; Night: 1,8,9
                    5-31-1989; Night: 2,9,3
                    6-1-1989; Night: 0,6,0
                    6-2-1989; Night: 6,5,4
                    6-3-1989; Night: 0,8,5
                    6-5-1989; Night: 5,5,6
                    6-6-1989; Night: 0,2,9
                    6-7-1989; Night: 3,7,4
                    6-8-1989; Night: 5,5,0
                    6-9-1989; Night: 9,3,0
                    6-10-1989; Night: 0,7,9
                    6-12-1989; Night: 7,9,6
                    6-13-1989; Night: 6,8,6

                    They are separated by ; : and by ,

                    You want all the separators to be "," so you are first going to replace:

                    The ; with ,

                    Then on the second pass you will replace the : with ,

                    -----------

                    Just wait a moment longer OK?

                    BibleOnline  ParishesOnline  ChristianRadioOnline   MassOnline   Mass

                    "Ten measures of beauty descended to the world, nine were taken by Jerusalem."

                      CARBOB's avatar - FL LOTTERY_LOGO.png
                      ORLANDO, FLORIDA
                      United States
                      Member #4924
                      June 3, 2004
                      5973 Posts
                      Offline
                      Posted: November 29, 2009, 10:09 am - IP Logged

                      After I clicked on 'Edit' and the 'Find and Replace' box comes up....it has two blank areas in which to type info.

                      Find: ____________________

                      Replace: _________________

                      When I type in 'Pick3_ASCII.doc' in the 'Find' area and 'Pick3_ASCII.csv' in the replace area, another box comes up and indicates that Excel cannot find what I've typed in.

                       

                      Still trying....

                      Which version of excel are you using? You should be able to import without all the replace>

                        LANTERN's avatar - kilroy 28_173_reasonably_small.jpg
                        Tx
                        United States
                        Member #4570
                        May 4, 2004
                        5180 Posts
                        Offline
                        Posted: November 29, 2009, 10:13 am - IP Logged

                        Go to Edit then to Replace then on the Replace box where it says:

                        Find what

                        Enter

                        ;

                        Then on the Replace with enter

                        ,

                        Then you will get:

                        Results for Pick 3                                                                                                                                                                                                                                                         

                        5-22-1989, Night: 9,1,4
                        5-23-1989, Night: 5,2,2
                        5-24-1989, Night: 2,3,9
                        5-25-1989, Night: 7,4,5
                        5-26-1989, Night: 9,6,7
                        5-27-1989, Night: 7,0,1
                        5-29-1989, Night: 6,5,9
                        5-30-1989, Night: 1,8,9
                        5-31-1989, Night: 2,9,3
                        6-1-1989, Night: 0,6,0
                        6-2-1989, Night: 6,5,4
                        6-3-1989, Night: 0,8,5
                        6-5-1989, Night: 5,5,6
                        6-6-1989, Night: 0,2,9
                        6-7-1989, Night: 3,7,4
                        6-8-1989, Night: 5,5,0
                        6-9-1989, Night: 9,3,0
                        6-10-1989, Night: 0,7,9
                        6-12-1989, Night: 7,9,6
                        6-13-1989, Night: 6,8,6

                        That was using the Replace All function, now you will do the same but

                        you will first enter

                        :

                        And then replace that with

                        ,

                        Like this:

                        Results for Pick 3                                                                                                                                                                                                                                                         

                        5-22-1989, Night, 9,1,4
                        5-23-1989, Night, 5,2,2
                        5-24-1989, Night, 2,3,9
                        5-25-1989, Night, 7,4,5
                        5-26-1989, Night, 9,6,7
                        5-27-1989, Night, 7,0,1
                        5-29-1989, Night, 6,5,9
                        5-30-1989, Night, 1,8,9
                        5-31-1989, Night, 2,9,3
                        6-1-1989, Night, 0,6,0
                        6-2-1989, Night, 6,5,4
                        6-3-1989, Night, 0,8,5
                        6-5-1989, Night, 5,5,6
                        6-6-1989, Night, 0,2,9
                        6-7-1989, Night, 3,7,4
                        6-8-1989, Night, 5,5,0
                        6-9-1989, Night, 9,3,0
                        6-10-1989, Night, 0,7,9
                        6-12-1989, Night, 7,9,6
                        6-13-1989, Night, 6,8,6

                        BibleOnline  ParishesOnline  ChristianRadioOnline   MassOnline   Mass

                        "Ten measures of beauty descended to the world, nine were taken by Jerusalem."

                          Play4Keeps's avatar - 656f0b5f5f1f6b58
                          End Of The Rainbow
                          United States
                          Member #67807
                          December 5, 2008
                          489 Posts
                          Offline
                          Posted: November 29, 2009, 10:13 am - IP Logged

                          O.k. Thanks Lantern.

                           

                          Carbob...I'm using 2003 version.

                          In Everything You Do.....PLAY 4 KEEPS!

                          Lots of luck, everyone!!

                                   





                            LANTERN's avatar - kilroy 28_173_reasonably_small.jpg
                            Tx
                            United States
                            Member #4570
                            May 4, 2004
                            5180 Posts
                            Offline
                            Posted: November 29, 2009, 10:15 am - IP Logged

                            The last thing is to save the text file with a .CSV ending and then just click on it.

                            All takes about 1 minute to do or less.

                            BibleOnline  ParishesOnline  ChristianRadioOnline   MassOnline   Mass

                            "Ten measures of beauty descended to the world, nine were taken by Jerusalem."

                              Play4Keeps's avatar - 656f0b5f5f1f6b58
                              End Of The Rainbow
                              United States
                              Member #67807
                              December 5, 2008
                              489 Posts
                              Offline
                              Posted: November 29, 2009, 10:15 am - IP Logged

                              Go to Edit then to Replace then on the Replace box where it says:

                              Find what

                              Enter

                              ;

                              Then on the Replace with enter

                              ,

                              Then you will get:

                              Results for Pick 3                                                                                                                                                                                                                                                         

                              5-22-1989, Night: 9,1,4
                              5-23-1989, Night: 5,2,2
                              5-24-1989, Night: 2,3,9
                              5-25-1989, Night: 7,4,5
                              5-26-1989, Night: 9,6,7
                              5-27-1989, Night: 7,0,1
                              5-29-1989, Night: 6,5,9
                              5-30-1989, Night: 1,8,9
                              5-31-1989, Night: 2,9,3
                              6-1-1989, Night: 0,6,0
                              6-2-1989, Night: 6,5,4
                              6-3-1989, Night: 0,8,5
                              6-5-1989, Night: 5,5,6
                              6-6-1989, Night: 0,2,9
                              6-7-1989, Night: 3,7,4
                              6-8-1989, Night: 5,5,0
                              6-9-1989, Night: 9,3,0
                              6-10-1989, Night: 0,7,9
                              6-12-1989, Night: 7,9,6
                              6-13-1989, Night: 6,8,6

                              That was using the Replace All function, now you will do the same but

                              you will first enter

                              :

                              And then replace that with

                              ,

                              Like this:

                              Results for Pick 3                                                                                                                                                                                                                                                         

                              5-22-1989, Night, 9,1,4
                              5-23-1989, Night, 5,2,2
                              5-24-1989, Night, 2,3,9
                              5-25-1989, Night, 7,4,5
                              5-26-1989, Night, 9,6,7
                              5-27-1989, Night, 7,0,1
                              5-29-1989, Night, 6,5,9
                              5-30-1989, Night, 1,8,9
                              5-31-1989, Night, 2,9,3
                              6-1-1989, Night, 0,6,0
                              6-2-1989, Night, 6,5,4
                              6-3-1989, Night, 0,8,5
                              6-5-1989, Night, 5,5,6
                              6-6-1989, Night, 0,2,9
                              6-7-1989, Night, 3,7,4
                              6-8-1989, Night, 5,5,0
                              6-9-1989, Night, 9,3,0
                              6-10-1989, Night, 0,7,9
                              6-12-1989, Night, 7,9,6
                              6-13-1989, Night, 6,8,6

                              Ok...lemme give that a try.

                              One moment, please.

                              In Everything You Do.....PLAY 4 KEEPS!

                              Lots of luck, everyone!!