Find Missing Numbers in a List

[Guest]

Was wondering if its possible to find the missing numbers in a list and
export to a new column. For example if the list is 1, 2, 3, 5, 7, 8- can I
have the missing 4 and 6 and put it in a new column? I have a list of 30,000
numbers so it's very tedious. Please help, thanks

 

[Otto Moehrbach]

Millie
I'll try to help you with this. Are your numbers actual numbers or do
they contain any letters? Is your data sorted by these numbers? Do any of
the numbers start with zeros (one or more zeros)? HTH Otto

 

[Biff]

Hi!

This method will work but it's slow. Slow = may take a minute or two to
calculate 30,000 possibilities. But hey, slow is faster and better than
tedious! After it's done you should convert the formulas to constants by
doing a Copy/Paste Special>Values.

Assume your number sequence is 1 to 30,000 and is in the range A1:A5000.

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=SMALL(IF(ISNA(MATCH(ROW($1:$30000),A$1:A$5000,0)),ROW($1:$30000),ROW(A1))

Copy down until you get #NUM! errors meaning all the missing values have
been returned.

Biff

 

[Biff]

Here's a macro by JMB. It's significantly faster on large sequences like
yours.

Sub FindMissing()
Dim lngUpper As Long
Dim lngLower As Long
Dim i As Long
Dim rngData As Range
Dim lngcount As Long

Set rngData = Range("C1:C1000") 'change as needed
lngLower = 1 'start of sequence change as needed
lngUpper = 5000 'end of sequence change as needed
lngcount = 1

For i = lngLower To lngUpper
If Not IsNumeric(Application.Match(i, _
rngData, 0)) Then
Range("D" & lngcount).Value = i 'sets output to column D
lngcount = lngcount + 1
End If
Next i

End Sub

Biff

 

[Guest]

Dear Biff, I was looking for a solution to the exact same problem "finding
missing numbers". But, I am not this smart. I don't know how to input this
macro??? Can you help me. Thanks so much. This would solve a two year
dilemma for me.

 

[Guest]

Ok, i figured out how to enter it, but when I try to run macro it's giving me
an error message "end if with out block it"??? can you help?

 

[Otto Moehrbach]

The line that starts with "If Not Isnumeric....." should end with "Then".
If it doesn't, then you have run into the line wrap that happens with these
messages. Make sure all of that is on one line. HTH Otto