Welcome Guest
Log In | Register )
You last visited January 16, 2017, 11:54 pm
All times shown are
Eastern Time (GMT-5:00)

Can anyone help me with Excel?

Topic closed. 10 replies. Last post 7 years ago by Sage.

Page 1 of 1
PrintE-mailLink
Sage's avatar - Lottery-035.jpg
Dunwoody Georgia
United States
Member #747
September 29, 2002
659 Posts
Offline
Posted: March 23, 2010, 3:19 pm - IP Logged

I'm trying to figure this out. Let's 

 

  say I  have the digits 1 2 3 4 5  and I  want to know what digits are missing =0 6 7 8 9

 

How can I program this in Excel?  Do I need and XL formula or macros? Can somebody write me the formula so I can just put it into my spreadsheet?

 Please help!  Thank you!

It's Never Too Late To Be The Person You Could Of Been!I Agree!

    Raven62's avatar - binary
    New Jersey
    United States
    Member #17843
    June 28, 2005
    50995 Posts
    Offline
    Posted: March 23, 2010, 3:40 pm - IP Logged

    http://www.lotterypost.com/thread/209623/1571102

    Lottery Post Forums Search is Your Friend!

    http://www.lotterypost.com/search?q=excel+missing+digits&t=all

    A mind once stretched by a new idea never returns to its original dimensions!

      Sage's avatar - Lottery-035.jpg
      Dunwoody Georgia
      United States
      Member #747
      September 29, 2002
      659 Posts
      Offline
      Posted: March 23, 2010, 9:50 pm - IP Logged

      Wow that's why I love this place!  I'm going to see if it works!  Thank you for posting and caring!  If I can do anything for you just let me know.  If I can I will believe me!  Party

      It's Never Too Late To Be The Person You Could Of Been!I Agree!

        lotteryprophet's avatar - nbvc
        lincoln, nebraska
        United States
        Member #86998
        February 15, 2010
        21 Posts
        Offline
        Posted: March 24, 2010, 11:07 am - IP Logged

        fibonacci had similar question and posted an answer I believe.

        http://www.lotterypost.com/thread/209623

          Sage's avatar - Lottery-035.jpg
          Dunwoody Georgia
          United States
          Member #747
          September 29, 2002
          659 Posts
          Offline
          Posted: March 26, 2010, 4:08 am - IP Logged

          Thanks for some reason that didn't work?

          It's Never Too Late To Be The Person You Could Of Been!I Agree!

            CARBOB's avatar - FL LOTTERY_LOGO.png
            ORLANDO, FLORIDA
            United States
            Member #4924
            June 3, 2004
            5961 Posts
            Offline
            Posted: March 26, 2010, 5:35 am - IP Logged

            Thanks for some reason that didn't work?

            What type of message did you get, don't just say," for some reason that didn't work?" It works if done right. Did you enter the 6 digits in A1? Did you enter the formula in B1? Did you name the formula in B1, in Name Manager, Digits works just fine.

              Sage's avatar - Lottery-035.jpg
              Dunwoody Georgia
              United States
              Member #747
              September 29, 2002
              659 Posts
              Offline
              Posted: March 27, 2010, 1:36 am - IP Logged

              Thanks Carbob. I'm not sure. Maybe it's something I'm doing wrong on my part.  Can you help me?

              It's Never Too Late To Be The Person You Could Of Been!I Agree!

                CARBOB's avatar - FL LOTTERY_LOGO.png
                ORLANDO, FLORIDA
                United States
                Member #4924
                June 3, 2004
                5961 Posts
                Offline
                Posted: March 27, 2010, 6:33 am - IP Logged

                Thanks Carbob. I'm not sure. Maybe it's something I'm doing wrong on my part.  Can you help me?

                Read very carefully, any questions, ask.

                Open a new workbook.

                Step 1

                Add this fucntion to VB (alt F11) Insert Module

                Copy and paste the macro, in red into module. exit VB.

                Function aconcat(a As Variant, Optional sep As String = "") As String
                ' Harlan Grove, Mar 2002
                Dim y As Variant

                If TypeOf a Is Range Then
                For Each y In a.Cells
                aconcat = aconcat & y.Value & sep
                Next y
                ElseIf IsArray(a) Then
                For Each y In a
                aconcat = aconcat & y & sep
                Next y
                Else
                aconcat = aconcat & a & sep
                End If

                aconcat = Left(aconcat, Len(aconcat) - Len(sep))
                End Function

                Step 2 the formula (assuming 6-digit string is in A1) Enter 6 digits in A1

                Enter the formula below in B1

                =aconcat(IF(ISERR(SEARCH({0,1,2,3,4,5,6,7,8,9},A1)),{0,1,2,3,4,5,6,7,8,9},""))

                Don't know which version you are using, you will need to give the formula a name in name manager. I named it Digits.

                Run macro.

                  sysp34's avatar - Lottery-062.jpg
                  Heroic City
                  Indonesia
                  Member #31689
                  February 2, 2006
                  1153 Posts
                  Offline
                  Posted: March 27, 2010, 8:44 am - IP Logged

                  Read very carefully, any questions, ask.

                  Open a new workbook.

                  Step 1

                  Add this fucntion to VB (alt F11) Insert Module

                  Copy and paste the macro, in red into module. exit VB.

                  Function aconcat(a As Variant, Optional sep As String = "") As String
                  ' Harlan Grove, Mar 2002
                  Dim y As Variant

                  If TypeOf a Is Range Then
                  For Each y In a.Cells
                  aconcat = aconcat & y.Value & sep
                  Next y
                  ElseIf IsArray(a) Then
                  For Each y In a
                  aconcat = aconcat & y & sep
                  Next y
                  Else
                  aconcat = aconcat & a & sep
                  End If

                  aconcat = Left(aconcat, Len(aconcat) - Len(sep))
                  End Function

                  Step 2 the formula (assuming 6-digit string is in A1) Enter 6 digits in A1

                  Enter the formula below in B1

                  =aconcat(IF(ISERR(SEARCH({0,1,2,3,4,5,6,7,8,9},A1)),{0,1,2,3,4,5,6,7,8,9},""))

                  Don't know which version you are using, you will need to give the formula a name in name manager. I named it Digits.

                  Run macro.

                  the vb code work perfectly, just use Carbob description and tada... :) we got like this

                   

                   AB
                  10123456789
                  20123456789
                  30123456789
                  40123456789
                  50123456789
                  6012345678
                  70123456789
                  80123456789
                  90123456789
                  100123456789

                   

                  Spreadsheet Formulas
                  CellFormula
                  B1=aconcat(IF(ISERR(SEARCH({0,1,2,3,4,5,6,7,8,9},A1)),{0,1,2,3,4,5,6,7,8,9},""))
                  B2=aconcat(IF(ISERR(SEARCH({0,1,2,3,4,5,6,7,8,9},A2)),{0,1,2,3,4,5,6,7,8,9},""))
                  B3=aconcat(IF(ISERR(SEARCH({0,1,2,3,4,5,6,7,8,9},A3)),{0,1,2,3,4,5,6,7,8,9},""))
                  B4=aconcat(IF(ISERR(SEARCH({0,1,2,3,4,5,6,7,8,9},A4)),{0,1,2,3,4,5,6,7,8,9},""))
                  B5=aconcat(IF(ISERR(SEARCH({0,1,2,3,4,5,6,7,8,9},A5)),{0,1,2,3,4,5,6,7,8,9},""))
                  B6=aconcat(IF(ISERR(SEARCH({0,1,2,3,4,5,6,7,8,9},A6)),{0,1,2,3,4,5,6,7,8,9},""))
                  B7=aconcat(IF(ISERR(SEARCH({0,1,2,3,4,5,6,7,8,9},A7)),{0,1,2,3,4,5,6,7,8,9},""))
                  B8=aconcat(IF(ISERR(SEARCH({0,1,2,3,4,5,6,7,8,9},A8)),{0,1,2,3,4,5,6,7,8,9},""))
                  B9=aconcat(IF(ISERR(SEARCH({0,1,2,3,4,5,6,7,8,9},A9)),{0,1,2,3,4,5,6,7,8,9},""))
                  B10=aconcat(IF(ISERR(SEARCH({0,1,2,3,4,5,6,7,8,9},A10)),{0,1,2,3,4,5,6,7,8,9},""))

                  good luck

                  Et erunt signa in sole.......Et luna et stellis.......Et presura gentium
                  Prae confusione sonitus maris.


                    winsumloosesum's avatar - Lottery-060.jpg
                    Pennsylvania
                    United States
                    Member #2218
                    September 1, 2003
                    5396 Posts
                    Offline
                    Posted: March 27, 2010, 8:45 am - IP Logged

                    Thanks Carbob. I'm not sure. Maybe it's something I'm doing wrong on my part.  Can you help me?

                    Try this.  Excel 2002

                    http://www.box.net/shared/ibcqxb36us

                    Enter your 5 digits in column a and drag down the formula(s) in column b

                      Sage's avatar - Lottery-035.jpg
                      Dunwoody Georgia
                      United States
                      Member #747
                      September 29, 2002
                      659 Posts
                      Offline
                      Posted: March 29, 2010, 10:07 pm - IP Logged

                      Thank you everyone it works perfect Carbob was correct!  Love you guys at ther Lottery Post!  Thank YOU!   Dance Party

                      It's Never Too Late To Be The Person You Could Of Been!I Agree!