Display missing sequencial numbers

I have a sheet where one column has sequencial numbers from lowest to highest with some missing. So for example: A1 1, A2 3, A3 5, A4 6. Is there a formula that will display all the numbers that are missing from this column without skipping rows? So let's say: B1 2, B2 4 ect.

Basically I will be putting this on a different tab, so someone can go to that tab and see what numbers have not been used. It is a large range and is difficult for someone to scan through it all.

Thanks for any assistance.

You could do this with VBA easily but i am not sure it can just be done in a worksheet function

I agree with amotto11. This would be much easier with VBA.

Attached is as near as I can get so far.
Enter the numbers in column A.
Enter the expected sequence in column C (Just enter the starting number of the sequence then drag the corner to fill the series)
Column D returns the missing numbers (however it does skip rows).

true VBA is the best and easy option for this

Try with formula:
Assuming list from A2:A37
In B2:

=IF(COUNT($B$1:B1)+COUNT($A$1:$A$37)>=MAX($A$2:$A$37),"",SMALL(IF(FREQUENCY($A$2:$A$37,ROW($A$1:INDIRECT("A"&MAX($A:$A))))=0,ROW($A$1:INDIRECT("A"&MAX($A:$A)+1)),""),ROW(1:1)))

Confirmed with Ctrl-Shift- Enter
Drag down

Originally Posted by bebo021999

Try with formula

Wow. I didn't think this could be solved by using formulas only. Very impressive +1.

Originally Posted by mc84excel

Wow. I didn't think this could be solved by using formulas only. Very impressive +1.

Yep. Except if data size is too large.

What version of Excel are you using?

Try this and we'll tweak it once we know what version of Excel you're using.

Let's assume the sequence of numbers is 1 to 6.

Enter this array formula** in B1:

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:6")),A$1:A$4,0)),ROW(INDIRECT("1:6"))),ROWS(B$1:B1))

** 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 start getting errors. We can remove the errors but need to know what version of Excel you're using so we can use the best method.