Find Missing Number?

D

Djanvk

Ok probably a easy answer here, but I'm a excel Novice.

what I have is a series of numbers and what I want to find is what
Number are missing in between all the different numbers to complete a
whole set of number.

Example

I have: 1 2 6 7 9 13

How could I find whats missing as to make it straight count up from 1 to 13

And return from this example 3 4 5 8 10 11 12

Thanks for any help
 
A

ANdras

Hi there,

one possibility is
in an additional column or sheet, i would create the whole series (type A1:
1, A2: = A1+1, then copy downwards until you have the required series)
then, next to this, so column B, IN b2:
=IF(ISNA(VLOOKUP(A2;THEOTHERSHEET_RANGE;1;0));"MISSING";"OK")

where: THEOTHERSHEET_RANGE is a range's 1st column is the original (and
gapped) series of numbers N.B. absolute cell reference with $-s, copy to
downwards with autofill and then you can see, which numbers are IN ("OK") and
which are missing /won't find out :) / the "MISSING". then you can use the
DATA/ Autofilter/Set Autofilter and filter to MISSING

is this, what you are after?

Best regards,
ANdras
(Hungary)
 
R

Ron Coderre

Try something like this:

With your list of values in Cells A1:A10 (eg 1,2,6,7,9,13)

This ARRAY FORMULA lists the items in ascending order.
B1:
=INDEX(ROW($A$1:INDEX(A:A,MAX(A:A))),SMALL(IF(COUNTIF($A$1:INDEX(A:A,MAX(A:A)),ROW($A$1:INDEX(A:A,MAX(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: To commit array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Copy B1 and paste it into B2 and down as far as you need


Does that help?

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

XL2002, WinXP-Pro
 
C

Cherith Cutestory

Hey this worked good...

thanks, still going to try the other forumlas also

thanks

Ron said:
Try something like this:

With your list of values in Cells A1:A10 (eg 1,2,6,7,9,13)

This ARRAY FORMULA lists the items in ascending order.
B1:
=INDEX(ROW($A$1:INDEX(A:A,MAX(A:A))),SMALL(IF(COUNTIF($A$1:INDEX(A:A,MAX(A:A)),ROW($A$1:INDEX(A:A,MAX(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: To commit array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Copy B1 and paste it into B2 and down as far as you need


Does that help?

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

XL2002, WinXP-Pro


Djanvk said:
Ok probably a easy answer here, but I'm a excel Novice.

what I have is a series of numbers and what I want to find is what
Number are missing in between all the different numbers to complete a
whole set of number.

Example

I have: 1 2 6 7 9 13

How could I find whats missing as to make it straight count up from 1 to 13

And return from this example 3 4 5 8 10 11 12

Thanks for any help
Click to expand...
Click to expand...
 
Top