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

Need help with an XL formula excell

Topic closed. 2 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: January 20, 2010, 2:10 am - IP Logged

Could you help me? 

say have 1 2 3 4 5  now I want to know what digits are missing =0 6 7 8 9
 
Can you tell me how to do this in xl formula?  I want to put this in a spreadsheet.  Thanks  Hyper

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

    Avatar

    United States
    Member #41846
    June 23, 2006
    460 Posts
    Offline
    Posted: January 20, 2010, 9:00 pm - IP Logged

    Sage

    if you use VBA this will do it

     

    Sub find_missing()
    Dim md(10) ' set an array with 10 elements or positions
    For x = 1 To 5 ' if your data is not in col a-e change these #'s
    a = Cells(1, x): md(a) = 1 ' if row1,col1 =5 then md(5)=1 i'll use this later
    Next x ' completes the loop  each array element that matches one of your numbers now equalls 1


    z = 6: For y = 0 To 9 'this loop will put the missing numbers in row1 col6-10
    If md(y) = 1 Then GoTo skip Else Cells(1, z) = y: z = z + 1 ' ignore if value is 1, display and inc pointer
    skip:
    Next y


    End Sub

     

     

    if you are new to VBA copy this bit of code. start a new excel sheet.press alt f8 under macro name enter

    find_missing   then click on create, right click and paste. you will need to delete the first and last line above.close your macro.  enter 5 numbers in row1 col a-e:press alt f8  and run your macro

    it sounds a lot more complicated than it is

    good luck

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

      Wow thank you so much!  I can't thank you enough.   Happy New Years to you.  There is no place like Lotery Post.

       

      Party

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