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

Need some Excel Help...

Topic closed. 19 replies. Last post 9 years ago by sysp34.

Page 2 of 2
PrintE-mailLink
Rakster's avatar - praying hands.jpg
Saskatchewan
Canada
Member #19992
August 9, 2005
2867 Posts
Offline
Posted: April 25, 2008, 1:15 pm - IP Logged

Rakster, another option, which will work better for the way LP formats the dates is to select "fixed width" instead of "delimited" and then you can create a break between the date and the pick 3 results so that your data imports into two columns like you want.  I hope this helps.

I appreciate all the help, but where do you find this "fixed width"?

We are all Lucky... just some of us don't realize it!

    Rakster's avatar - praying hands.jpg
    Saskatchewan
    Canada
    Member #19992
    August 9, 2005
    2867 Posts
    Offline
    Posted: April 25, 2008, 1:23 pm - IP Logged

    I appreciate all the help, but where do you find this "fixed width"?

    I think its got something to do with the dashes in between the numbers... like 6-7-8 instead of just having 678.

    Once I deleted these dashes all is ok.. .so I might as well do it manually as I wasted a lot of time with this stupid program and I am no further ahead...lol

    I appreciate everyones input and help.. .thank you all so much. Smile

    We are all Lucky... just some of us don't realize it!

      Avatar
      New Mexico
      United States
      Member #58526
      February 18, 2008
      683 Posts
      Offline
      Posted: April 25, 2008, 1:39 pm - IP Logged

      Ah, okay, Rakster.  I will explain the way I get data into excel.  It's a little bit more complicated, but it works great.

      1.  Create a new blank text document and name it "pick3results" or whatever you want.

      2.  Paste the data from LP into the text document and save it.

      3.  Open up a new excel file.

      4.  Select the "Data" tab, "Import External Data...", "Import Data..."

      5.  Locate the text file you created and select open.

      6.  Click the "Delimited" circle and then "next"

      7.  Check the "space" box and then "next"

      8.  Now choose which columns you want to export and "finish"

      In step 6 above, instead of selecting "Delimited", select "Fixed Width"..............it's sort of a pain, but once you get used to importing data into excel this way, I think you will find it's very flexible and useful.

        Rakster's avatar - praying hands.jpg
        Saskatchewan
        Canada
        Member #19992
        August 9, 2005
        2867 Posts
        Offline
        Posted: April 25, 2008, 1:45 pm - IP Logged

        In step 6 above, instead of selecting "Delimited", select "Fixed Width"..............it's sort of a pain, but once you get used to importing data into excel this way, I think you will find it's very flexible and useful.

        Thanks Buddy!

        We are all Lucky... just some of us don't realize it!

          sysp34's avatar - Lottery-062.jpg
          Heroic City
          Indonesia
          Member #31689
          February 2, 2006
          1153 Posts
          Offline
          Posted: April 25, 2008, 9:46 pm - IP Logged

          Heres what it looks like on a fresh worksheet... on regular paste.

           

          Thu, Apr 24, 20082/6/2007
          Wed, Apr 23, 20084/8/2003
          Tue, Apr 22, 20089/2/2004
          Mon, Apr 21, 20088-0-9

           

          809 appears but the rest show a date???

          This is what it looks like if I use special paste and unicode...

           

          Thu, Apr 24, 2008 2-6-7 
          Wed, Apr 23, 2008 4-8-3 
          Tue, Apr 22, 2008 9-2-4 
          Mon, Apr 21, 2008 8-0-9 
          Sat, Apr 19, 2008 3-4-0 
          Fri, Apr 18, 2008 2-6-6 
          Thu, Apr 17, 2008 8-7-3 
          Wed, Apr 16, 2008 6-7-5 
          Tue, Apr 15, 2008 3-8-1 
          Mon, Apr 14, 2008 8-9-0 
          Sat, Apr 12, 2008 9-7-5 
          Fri, Apr 11, 2008 6-9-9 

           

          All in one cell...

          Rakster

          why not use mid function and give month reference for example this formula

           

          Jan01
          Thu, Apr 24, 2008 2-6-7  =INDEX($B$1:$B$12,MATCH(MID(D1,6,3),$A$1:$A$12,0))&"/"&MID(D1,10,2)&"/"&MID(D1,14,4)
          Feb02
          Wed, Apr 23, 2008 4-8-3  =INDEX($B$1:$B$12,MATCH(MID(D2,6,3),$A$1:$A$12,0))&"/"&MID(D2,10,2)&"/"&MID(D2,14,4)
          Mar03
          Tue, Apr 22, 2008 9-2-4  =INDEX($B$1:$B$12,MATCH(MID(D3,6,3),$A$1:$A$12,0))&"/"&MID(D3,10,2)&"/"&MID(D3,14,4)
          Apr04
          Mon, Apr 21, 2008 8-0-9  =INDEX($B$1:$B$12,MATCH(MID(D4,6,3),$A$1:$A$12,0))&"/"&MID(D4,10,2)&"/"&MID(D4,14,4)
          May05
          Sat, Apr 19, 2008 3-4-0  =INDEX($B$1:$B$12,MATCH(MID(D5,6,3),$A$1:$A$12,0))&"/"&MID(D5,10,2)&"/"&MID(D5,14,4)
          Jun06
          Fri, Apr 18, 2008 2-6-6  =INDEX($B$1:$B$12,MATCH(MID(D6,6,3),$A$1:$A$12,0))&"/"&MID(D6,10,2)&"/"&MID(D6,14,4)
          Jul07
          Thu, Apr 17, 2008 8-7-3  =INDEX($B$1:$B$12,MATCH(MID(D7,6,3),$A$1:$A$12,0))&"/"&MID(D7,10,2)&"/"&MID(D7,14,4)
          Aug08
          Wed, Apr 16, 2008 6-7-5  =INDEX($B$1:$B$12,MATCH(MID(D8,6,3),$A$1:$A$12,0))&"/"&MID(D8,10,2)&"/"&MID(D8,14,4)
          Sep09
          Tue, Apr 15, 2008 3-8-1  =INDEX($B$1:$B$12,MATCH(MID(D9,6,3),$A$1:$A$12,0))&"/"&MID(D9,10,2)&"/"&MID(D9,14,4)
          Oct10
          Mon, Apr 14, 2008 8-9-0  =INDEX($B$1:$B$12,MATCH(MID(D10,6,3),$A$1:$A$12,0))&"/"&MID(D10,10,2)&"/"&MID(D10,14,4)
          Nov11
          Sat, Apr 12, 2008 9-7-5  =INDEX($B$1:$B$12,MATCH(MID(D11,6,3),$A$1:$A$12,0))&"/"&MID(D11,10,2)&"/"&MID(D11,14,4)
          Dec12
          Fri, Apr 11, 2008 6-9-9  =INDEX($B$1:$B$12,MATCH(MID(D12,6,3),$A$1:$A$12,0))&"/"&MID(D12,10,2)&"/"&MID(D12,14,4)

           

          Jan01
          Thu, Apr 24, 2008 2-6-7  04/24/2008
          Feb02
          Wed, Apr 23, 2008 4-8-3  04/23/2008
          Mar03
          Tue, Apr 22, 2008 9-2-4  04/22/2008
          Apr04
          Mon, Apr 21, 2008 8-0-9  04/21/2008
          May05
          Sat, Apr 19, 2008 3-4-0  04/19/2008
          Jun06
          Fri, Apr 18, 2008 2-6-6  04/18/2008
          Jul07
          Thu, Apr 17, 2008 8-7-3  04/17/2008
          Aug08
          Wed, Apr 16, 2008 6-7-5  04/16/2008
          Sep09
          Tue, Apr 15, 2008 3-8-1  04/15/2008
          Oct10
          Mon, Apr 14, 2008 8-9-0  04/14/2008
          Nov11
          Sat, Apr 12, 2008 9-7-5  04/12/2008
          Dec12
          Fri, Apr 11, 2008 6-9-9  04/11/2008

           

          good luck