Completion of a series of numbers that are missing

[bines53]

Hello,

I have a column of numbers from 1 to 10 I need to find the missing numbers in the same column.
For example ,Following numbers listed in column :1,2,3,7,8,10
I need a formula that will give the missing numbers : 4,5,6,9

Thank you !

 

[AIM]

@bines53 ,

Suppose your list of numbers (1 to 10) are in D11 to D17, enter the below formula in E11

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:"&MAX($D$11:$D$17))),$D$11:$D$17,0)),ROW(INDIRECT("1:"&MAX($D$11:$D$17)))),ROWS($D$10:D10)),"")

Press Ctrl+Shift+Enter as this is an array formula

Regards!!

 

[dan_l]

i just did this with vba. I'm not sure which is a bigger pita: the array megaformula or a sub to do it

 

[bines53]

AIM,

Thank you !

 

[Debraj]

@bines53

You can try lil bit smaller Array formula ..

=IFERROR(SMALL(IF(COUNTIF($D$11:$D$17,ROW($1:$10))=0,ROW($1:$10),""),ROW(A1)),"")

@AIM
Did you checked your formula with a sequence Number.. like 1,2,3...,7

 

[bines53]

Hi debraj ,

Can you attach a file, I could not how to implement your solution.

Thank you !

 

[Debraj]

Hi @bines53 ,

Suppose your list of numbers (1 to 10) are in D11 to D17, enter the formula in E11 with Ctrl + shift + Enter

 

[AIM]

Yes, Debraj I had checked the formulas with the numbers 1-10 in sequence

@bines53,

Here is the file with both the formulas

Regards!!

 

[Debraj]

Hi AIM..
No, i am not criticizing anything .. just wanted to let you know...MAX($D$11:$D$17) if Max is 8 in the series... it will miss 9 and 10.. and will check missing number between 1 and Max number in the series..

Please take this in a positive way..