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

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
"millie6169" wrote in message
...
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

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 SpecialValues.

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

"millie6169" wrote in message
...
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

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

"Biff" wrote in message
...
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 SpecialValues.

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

"millie6169" wrote in message
...
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

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.

"Biff" wrote:

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

"Biff" wrote in message
...
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 SpecialValues.

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

"millie6169" wrote in message
...
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

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?

"Biff" wrote:

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

"Biff" wrote in message
...
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 SpecialValues.

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

"millie6169" wrote in message
...
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

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
"jpfrmnm" wrote in message
...
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?

"Biff" wrote:

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

"Biff" wrote in message
...
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 SpecialValues.

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

"millie6169" wrote in message
...
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