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

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

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

Hi again Sir Biff,
i test it and works great for series of positive integers...
is there some more generic formula of yours for integers "- to +" series,
and another one for series of fix decimals e.g, 0.5,1,1.5,2,2.5....

--
*****
birds of the same feather flock together..



"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

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

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

I am not quite sure how to get the macro to work with an already created set
of commands. Could you instruct me on how to do that. Then I will try it to
see if it works.
--
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

Ok, let's try a small sample.

Assume you have a list of numbers in the range A1:A10. Those numbers a

2, 5, 7, 8, 10, 12, 15, 16, 17, 19

You want to find what numbers are missing from that list based on the
sequence of 1 to 20.

In the macro, where you see <<<<, those are the portions that you would need
to change based on the criteria. So, the sample criteria a the list of
numbers is in the range A1:A10 and the sequence to test is 1 to 20. 1 is the
lower boundary and 20 is the upper boundary. The output of the macro will
start in D1. Here's the macro with those criteria:

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("A1:A10") '<<<<change as needed
lngLower = 1 '<<<<start of sequence
lngUpper = 20 '<<<<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

Now, to run this macro:

Hit ALT F11 (hold down the ALT key then press function key F11)

This will open the VBA editor.

Goto the menu ViewProject Explorer (or hit CTRL R - hold down the CTRL key
then press R)

In the pane that opens find your file. It will look like this:

VBAProject(your_file_name)

Right click on your file name

Select InsertModule

Paste the macro in the window that opens on the right side of the screen.

Now, close the VBA editor and return back to your Excel file (click the
close "X")

Now you're ready to run the macro.

Goto the menu ToolsMacroMacros

Select the macro, FindMissing, click Run

Biff

"hana" wrote in message
...
I am not quite sure how to get the macro to work with an already created
set
of commands. Could you instruct me on how to do that. Then I will try it
to
see if it works.
--
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

Seems a bit confusing.
Sounds like you just ned to go and type the missing numbers into empty cells.
Do you have an example of what you are attempting?

"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

The list that I am using contains over 20,000 row. The formulas are quite
confusing but to me it seems easier and less time consuming to try to figure
the formula out than to put each row in by hand. Do you have any advice?
--
Hana


"Sean Timmons" wrote:

Seems a bit confusing.
Sounds like you just ned to go and type the missing numbers into empty cells.
Do you have an example of what you are attempting?

"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

Hi hana,
it depends on which passing criteria you need to base the numbers that must
be inserted as to be added....

is it a complete series...1,2,3,4 to forever
or 1,3,5,7,....all odd
or 2,4,6,8,....all even
or whatever jumping series you need to specify over here..

best regards,
dribler...
--
*****
birds of the same feather flock together..



"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

It is a complete series 1,2 3,4,5 to about 12,000. Do you have an easier way
to do it?
--
Hana


"dribler2" wrote:

Hi hana,
it depends on which passing criteria you need to base the numbers that must
be inserted as to be added....

is it a complete series...1,2,3,4 to forever
or 1,3,5,7,....all odd
or 2,4,6,8,....all even
or whatever jumping series you need to specify over here..

best regards,
dribler...
--
*****
birds of the same feather flock together..



"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

OK....then maybe something like this would be easier to follow:

If your sequence is in A1:A11000
beginning with 1, but containing gaps
AND
it should contain values 1 through 12000

Then
C1: =IF(ISNA(MATCH(ROW(A1),$A$1:$A$11000,0)),ROW(A1)," delete")
Copy that formula down through C12000

(That should list the missing numbers and "delete")
Then....
Select C1:C12000
<edit<copy
<edit<paste specialCheck: Values

Then...sort C1:C12000, ascending

The missing numbers will appear at the top of the list....copy them to the
list.
Clear the "delete" cells.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"hana" wrote:

It is a complete series 1,2 3,4,5 to about 12,000. Do you have an easier way
to do it?
--
Hana


"dribler2" wrote:

Hi hana,
it depends on which passing criteria you need to base the numbers that must
be inserted as to be added....

is it a complete series...1,2,3,4 to forever
or 1,3,5,7,....all odd
or 2,4,6,8,....all even
or whatever jumping series you need to specify over here..

best regards,
dribler...
--
*****
birds of the same feather flock together..



"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

Wow! That formula is a lot easier to understand than the other one.
However, a brilliant co-worker of mine understood your original formula
better than me and he got it to work as well. So now I have more than one
way to do this. I am set as long as I never lose these formulas.

I have to say that I am completely amazed by your knowledge of excel. Did
you understand and create these formulas off the top of your head? I am just
amazed. I didn't know that I knew so little about excel until I got your
assistance.

Thank you so much. It has been very useful.
--
Hana


"Ron Coderre" wrote:

OK....then maybe something like this would be easier to follow:

If your sequence is in A1:A11000
beginning with 1, but containing gaps
AND
it should contain values 1 through 12000

Then
C1: =IF(ISNA(MATCH(ROW(A1),$A$1:$A$11000,0)),ROW(A1)," delete")
Copy that formula down through C12000

(That should list the missing numbers and "delete")
Then....
Select C1:C12000
<edit<copy
<edit<paste specialCheck: Values

Then...sort C1:C12000, ascending

The missing numbers will appear at the top of the list....copy them to the
list.
Clear the "delete" cells.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"hana" wrote:

It is a complete series 1,2 3,4,5 to about 12,000. Do you have an easier way
to do it?
--
Hana


"dribler2" wrote:

Hi hana,
it depends on which passing criteria you need to base the numbers that must
be inserted as to be added....

is it a complete series...1,2,3,4 to forever
or 1,3,5,7,....all odd
or 2,4,6,8,....all even
or whatever jumping series you need to specify over here..

best regards,
dribler...
--
*****
birds of the same feather flock together..



"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

i agree
--
*****
birds of the same feather flock together..



"Ron Coderre" wrote:

OK....then maybe something like this would be easier to follow:

If your sequence is in A1:A11000
beginning with 1, but containing gaps
AND
it should contain values 1 through 12000

Then
C1: =IF(ISNA(MATCH(ROW(A1),$A$1:$A$11000,0)),ROW(A1)," delete")
Copy that formula down through C12000

(That should list the missing numbers and "delete")
Then....
Select C1:C12000
<edit<copy
<edit<paste specialCheck: Values

Then...sort C1:C12000, ascending

The missing numbers will appear at the top of the list....copy them to the
list.
Clear the "delete" cells.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"hana" wrote:

It is a complete series 1,2 3,4,5 to about 12,000. Do you have an easier way
to do it?
--
Hana


"dribler2" wrote:

Hi hana,
it depends on which passing criteria you need to base the numbers that must
be inserted as to be added....

is it a complete series...1,2,3,4 to forever
or 1,3,5,7,....all odd
or 2,4,6,8,....all even
or whatever jumping series you need to specify over here..

best regards,
dribler...
--
*****
birds of the same feather flock together..



"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