Welcome Guest
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
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

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

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

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.

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

 Page 1 of 1