Find Missing Numbers

Hi

I seek some help with the attached spreadsheet with find missing numbers in a sequence.

What it is that I want to achive is run a routine that looksa down a column of numbers and then dispay the missing number along side.

What I have at the moments is that it finds and displays a number, but if the numbers missing are more that one it only displays that one not the others.

Any ideas how I can do this.

Please see attached


Many thanks

Hi zbor

Many thanks for your response.

I followed your instructions but column E only dispays one missing number.

eg column
C10 = 9154051 E10 = 9154052 also needs to display 9154053
C11 = 9154054

Is this possible?

Many thanks

Did you pull formula to the right too?

Also, maybe this approach you could use too:

=IF($C11-$C10=2, C10+1, IF($C11-$C10>2, C10+1&" - "&$C11-1, ""))

Hi,

I have one more way
please check the attached file.

Hi zbor

Many thanks for that response, it did the trick

Hi Rahul Nagar

Many thanks for contribution. I like you concept works very vell. I did not consider Vlookup.

I will be able to use your concept as well

Hi Rahul Nagar

I do have one question about your method. In column D how do your get the word Missing Notices if there are some missing.

L cannot see how this is achieved?

Hi,

I have replace #N/A with "Missing Notices" using Replace option shortcut key is Ctrl+H.

Hi Rahul Nagar

Thanks for that information

Hi zbor

I have another question, if I may.

In your example =CHOOSE(MIN(3,$C11-$C10), "", C10+1, C10+1&" - "&$C11-1)

It displays the number in this format 9154058 - 9154061.

Is it possible to display all the missing number 9154058, 9154059, 9154060, 9154061

I appericate your assistance.

Many thanks

Hi zbor

Sorry for this, but I must be a bit thick.

I followed your instructions in post #2

I am confused what you mean by this

you need to go at least 151 column to the right

Hi zbor

I cannot that you enough for the time you spent on this. What I can now see is the I miss understood the

you need to go to the right

Once again many thanks

PS I will leave you in peace.

Hi

Further to my earlier post concerning missing numbers and to the great help provided by zbor.

This the last post received and it works.

Write this formula in E10:

=IF(COLUMNS($E$10:E$10)<$C11-$C10,$C10+COLUMNS($E$10:E$10),"")

And pull it right and down as much as you need.

Notice that for example:

9154100
9154251

you need to go at least 151 column to the right...

Maximum is 256

The above function writes the missing numbers in a new column.

I have a further question.

Is it possible to write the numbers into one cell so that I can then text wrap instead of

you need to go at least 151 column to the right...