ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Adding numbers to an incomplete numerical list (https://www.excelbanter.com/excel-discussion-misc-queries/125396-adding-numbers-incomplete-numerical-list.html)

hana
Adding numbers to an incomplete numerical list
 
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

Ron Coderre
Adding numbers to an incomplete numerical list
 
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

Sean Timmons
Adding numbers to an incomplete numerical list
 
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

dribler2
Adding numbers to an incomplete numerical list
 
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

T. Valko
Adding numbers to an incomplete numerical list
 
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



dribler2
Adding numbers to an incomplete numerical list
 
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




Ron Coderre
Adding numbers to an incomplete numerical list
 
It wasn't a complaint, Biff....just a concern. If the series begins with 1,
then it's a fine formula....but, if not, it unnecessarily burns through the
first "n" values.

You made a good point about the formula I posted (it maxes out at the sheet
row limit), so I enhanced it.

This ARRAY FORMULA is durable against list values larger than the row limit
of the sheet AND against a Max-Min difference greater than that same limit:

=INDEX(ROW($A$1:INDEX(A:A,MIN(ROWS(A:A),MAX(A:A)-MIN(A:A)))),SMALL(IF(COUNTIF($A$1:INDEX(A:A,MIN(RO WS(A:A),MAX(A:A)-MIN(A:A))),ROW($A$1:INDEX(A:A,MIN(ROWS(A:A),MAX(A: A)-MIN(A:A))))+MIN(A:A)-1)=0,ROW($A$1:INDEX(A:A,MIN(ROWS(A:A),MAX(A:A)-MIN(A:A))))+MIN(A:A)-1),ROWS($1:1))-MIN(A:A))+MIN(A:A)

Sample:
With
A1: 500,001
A2: 500,002
A3: 500,003
A4: 500,004
A5: 500,010
A6: 500,011
A7: 500,012
A8: 500,013
A9: 500,014
A10: 500,015

And that formula in B1:B5
The return values a
B1: 500,005
B2: 500,006
B3: 500,009
B4: 500,010
B5: 500,011

Note, though....if you try that example, it takes a few seconds to calc.

***********
Regards,
Ron

XL2002, WinXP


"T. Valko" wrote:

I based it on your sample:

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

It's not as robust as yours. It's a "quickie" that's specific to the
situation. And, using ROW/ROWS limits the sequence to what version of Excel
you have. 16k for old version, 65k for newer version, 1024k for 2007.

Biff

"Ron Coderre" wrote in message
...
Here's my only concern with that approach, Biff...

If the series doesn't start with 1, the formula will list from 1 up to the
first series value as missing.

Example:
A1:A10 contains
61
65
66
70
71
72
73
74
75
80

The formula you posted returns:
1
2
3
...up to 25, then returns #NUM values.

The formula I posted returns:
62
63
64
67
68
69
76
77
78
79

***********
Regards,
Ron

XL2002, WinXP


"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

Ron Coderre
Adding numbers to an incomplete numerical list
 
RATS! I failed to change the posted example to reflect the actual list I used
in A1:A10! Apologies.

--------------------
Amended Example:
Sample:
With
A1: 500,001
A2: 500,002
A3: 500,003
A4: 500,004
A5: 500,007
A6: 500,008
A7: 500,012
A8: 500,013
A9: 500,014
A10: 500,015

And that formula in B1:B5
The return values a
B1: 500,005
B2: 500,006
B3: 500,009
B4: 500,010
B5: 500,011

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

It wasn't a complaint, Biff....just a concern. If the series begins with 1,
then it's a fine formula....but, if not, it unnecessarily burns through the
first "n" values.

You made a good point about the formula I posted (it maxes out at the sheet
row limit), so I enhanced it.

This ARRAY FORMULA is durable against list values larger than the row limit
of the sheet AND against a Max-Min difference greater than that same limit:

=INDEX(ROW($A$1:INDEX(A:A,MIN(ROWS(A:A),MAX(A:A)-MIN(A:A)))),SMALL(IF(COUNTIF($A$1:INDEX(A:A,MIN(RO WS(A:A),MAX(A:A)-MIN(A:A))),ROW($A$1:INDEX(A:A,MIN(ROWS(A:A),MAX(A: A)-MIN(A:A))))+MIN(A:A)-1)=0,ROW($A$1:INDEX(A:A,MIN(ROWS(A:A),MAX(A:A)-MIN(A:A))))+MIN(A:A)-1),ROWS($1:1))-MIN(A:A))+MIN(A:A)

Sample:
With
A1: 500,001
A2: 500,002
A3: 500,003
A4: 500,004
A5: 500,010
A6: 500,011
A7: 500,012
A8: 500,013
A9: 500,014
A10: 500,015

And that formula in B1:B5
The return values a
B1: 500,005
B2: 500,006
B3: 500,009
B4: 500,010
B5: 500,011

Note, though....if you try that example, it takes a few seconds to calc.

***********
Regards,
Ron

XL2002, WinXP


"T. Valko" wrote:

I based it on your sample:

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

It's not as robust as yours. It's a "quickie" that's specific to the
situation. And, using ROW/ROWS limits the sequence to what version of Excel
you have. 16k for old version, 65k for newer version, 1024k for 2007.

Biff

"Ron Coderre" wrote in message
...
Here's my only concern with that approach, Biff...

If the series doesn't start with 1, the formula will list from 1 up to the
first series value as missing.

Example:
A1:A10 contains
61
65
66
70
71
72
73
74
75
80

The formula you posted returns:
1
2
3
...up to 25, then returns #NUM values.

The formula I posted returns:
62
63
64
67
68
69
76
77
78
79

***********
Regards,
Ron

XL2002, WinXP


"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

T. Valko
Adding numbers to an incomplete numerical list
 
It wasn't a complaint, Biff....just a concern.

Didn't take it as a complaint! I do the same thing!

If the series begins with 1, then it's a fine formula....but, if not,
it unnecessarily burns through the first "n" values.

All you need to do is change the ROW(...) range:

If the sequence is 60 to 90:

ROW($60:$90)

Biff

"Ron Coderre" wrote in message
...
It wasn't a complaint, Biff....just a concern. If the series begins with
1,
then it's a fine formula....but, if not, it unnecessarily burns through
the
first "n" values.

You made a good point about the formula I posted (it maxes out at the
sheet
row limit), so I enhanced it.

This ARRAY FORMULA is durable against list values larger than the row
limit
of the sheet AND against a Max-Min difference greater than that same
limit:

=INDEX(ROW($A$1:INDEX(A:A,MIN(ROWS(A:A),MAX(A:A)-MIN(A:A)))),SMALL(IF(COUNTIF($A$1:INDEX(A:A,MIN(RO WS(A:A),MAX(A:A)-MIN(A:A))),ROW($A$1:INDEX(A:A,MIN(ROWS(A:A),MAX(A: A)-MIN(A:A))))+MIN(A:A)-1)=0,ROW($A$1:INDEX(A:A,MIN(ROWS(A:A),MAX(A:A)-MIN(A:A))))+MIN(A:A)-1),ROWS($1:1))-MIN(A:A))+MIN(A:A)

Sample:
With
A1: 500,001
A2: 500,002
A3: 500,003
A4: 500,004
A5: 500,010
A6: 500,011
A7: 500,012
A8: 500,013
A9: 500,014
A10: 500,015

And that formula in B1:B5
The return values a
B1: 500,005
B2: 500,006
B3: 500,009
B4: 500,010
B5: 500,011

Note, though....if you try that example, it takes a few seconds to calc.

***********
Regards,
Ron

XL2002, WinXP


"T. Valko" wrote:

I based it on your sample:

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

It's not as robust as yours. It's a "quickie" that's specific to the
situation. And, using ROW/ROWS limits the sequence to what version of
Excel
you have. 16k for old version, 65k for newer version, 1024k for 2007.

Biff

"Ron Coderre" wrote in message
...
Here's my only concern with that approach, Biff...

If the series doesn't start with 1, the formula will list from 1 up to
the
first series value as missing.

Example:
A1:A10 contains
61
65
66
70
71
72
73
74
75
80

The formula you posted returns:
1
2
3
...up to 25, then returns #NUM values.

The formula I posted returns:
62
63
64
67
68
69
76
77
78
79

***********
Regards,
Ron

XL2002, WinXP


"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

hana
Adding numbers to an incomplete numerical list
 
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

hana
Adding numbers to an incomplete numerical list
 
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

hana
Adding numbers to an incomplete numerical list
 
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




Ron Coderre
Adding numbers to an incomplete numerical list
 
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

T. Valko
Adding numbers to an incomplete numerical list
 
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

hana
Adding numbers to an incomplete numerical list
 
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

dribler2
Adding numbers to an incomplete numerical list
 
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

hana
Adding numbers to an incomplete numerical list
 
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




T. Valko
Adding numbers to an incomplete numerical list
 
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


All times are GMT +1. The time now is 06:58 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com