The list that I am using contains over 20,000 row.

I would not use a formula for this big of a series. (except as a last
resort!)

Did you try the macro? It works and is significantly faster that any formula
for this size of a series.

If you need help changing it to suit your needs just give us the *EXACT
DETAILS* -- where is the list of numbers (EXACT location - like A1:A20000)?
What are the boundaries (start at 1 end at 20000)? Where do you want the
output?

Biff

"hana" wrote in message
...
I am sure that your formulas are very helpful but I am having trouble
applying them and getting any useful numbers out of them for my list. Do
you
have any suggestions?

--
Hana


"T. Valko" wrote:

Assuming that the range of numbers is in A1:A10 and that A1 = lower
boundary
and A10 = upper boundary:

Array entered:

=SMALL(IF(ISNA(MATCH(ROW($1:$25),A$1:A$10,0)),ROW( $1:$25)),ROW(A1))

This can be very slow on large sequences ~5,000+

Here's a macro by JMB:

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
lngUpper = 5000 'end of sequence
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

"Ron Coderre" wrote in message
...
Maybe something like this:

With your list of values in Cells A1:A10 (eg 1,2,5,6,10,11,15,20,21,25)

This ARRAY FORMULA lists the missing items in ascending order.
B1:
=INDEX(ROW($A$1:INDEX(A:A,MAX(A:A))),SMALL(IF(COUN TIF($A$1:INDEX(A:A,MAX(A:A)),ROW($A$1:INDEX(A:A,MA X(A:A)))+MIN(A:A)-1)=0,ROW($A$1:INDEX(A:A,MAX(A:A)))+MIN(A:A)-1),ROWS($1:1))-MIN(A:A))+MIN(A:A)

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Once the list of missing numbers is complete,
Copy/PasteSpecial(value) to the bottom of the actual list

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"hana" wrote:

I have a list of numbers w/ data that doesn't include all of the
numbers.
I
need to add numbers to the list to complete the numerical list (so the
added
numbers would have no data in the row, just the number). It seems
like
such
a simple thing to do and yet I have no idea how to actually do it.
Does
anyone know something that would help me to complete this list?
--
Hana