How to find missing numbers from a list in excel 2010?

[prabhakareord]

I am using excel- 2010. I want to find the missing numbers from a list of numbers sequence.
i tried the array formula by searching some sites on Google but is not working properly. i am getting only one missing number but unable to find all of them.
And also ia m having problem with amending the formula according to my need can any one explain be how to write the formula in-detail to find the missing numbers??

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[prabhakareord]

excel.JPG

I want to find the missing numbers from 11 to 64 in the list. Here FROM and TO in the sense, the numbers which are in between these cells are not required as missing but i need the numbers which are missing from the values mentioned in both columns. So how to find them using a formula or any other methods??

[FDibbins]

Pepe asked for a sample workbook, not a pic. No1 is inclined to re-type your data for you, and a pic is impossible to work with

[prabhakareord]

Book1.xlsx

This is the sample book.
I want to find the missing numbers from 11 to 64 in the list. Here FROM and TO in the sense, the numbers which are in between these cells are not required as missing but i need the numbers which are missing from the values mentioned in both columns. So how to find them using a formula or any other methods??

[Tony Valko]

Did you attach the correct file?

I looked at the attached file and I don't see where there are missing numbers.

Let's try this...

If you have these numbers in column A:

1, 3, 5

And you want to find which numbers are missing from the array 1, 2, 3, 4, 5. So, the missing numbers are 2 and 4.

Is that what you want to do?

[prabhakareord]

ok! can you plz tell me the formula to how o find missing numbers like that

for example 2 and 4 in the list of 1 to 5

can you tell me how to write the formula

[dredwolf]

I will offer you this :http://www.cpearson.com/excel/ListFunctions.aspx
It works if you have a list with the full range you are looking for, but the principle is the same --"find numbers not in one list that do exist in the other list"

The reason (I think) Tony Valko (Biff) asked you the question, is because your column A in the sample IS contigous, no missing numbers! and there is no indication of what is expected or what is wrong, so nothing to base a formula on..

Hope this helps

[kvsrinivasamurthy]

Pl see the attached file. It gives missing numbers between Two Numbers.

[Tony Valko]

Let's assume you have this data:

A2 = 1
A3 = 3
A4 = 5

You want to know which numbers are not listed from the sequence 1, 2, 3, 4, 5.

Enter this array formula** in C2:

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:5")),A$2:A$4,0)),ROW(INDIRECT("1:5"))),ROWS(C$2:C2)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Note that this formula is slow to calculate on very large data sets.

[prabhakareord]

Thanks! But i am only getting two , even though many times i am not getting 4

i am able to find only one number and unable to find another numbers ''

can you tell me how to solve this?

[Tony Valko]

Here's a small sample file that demonstrates this:

MissingNums.xlsx

The formula used in an array formula.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.