I have a list of numbers from 1 to 1000 in Column A. Some of the numbers are
missing; for example they may run 1, 2 3, 7 etc.

I need a formula that will calculate the missing numbers and place them in
Column B.

Many thanks in advance.

SiH,

In cell B1, enter the formula

=IF(ISERROR(MATCH(ROW(),A:A,FALSE)),ROW(),"")

and copy down to B2:B1000.

Copy all of B, paste special values, and then sort B ascending, and you will have your list.

HTH,
Bernie
MS Excel MVP


"SiH23" wrote in message
...
I have a list of numbers from 1 to 1000 in Column A. Some of the numbers are
missing; for example they may run 1, 2 3, 7 etc.

I need a formula that will calculate the missing numbers and place them in
Column B.

Many thanks in advance.

In A1 enter =IF(COUNTIF(A:A,ROW(A1)),"",ROW(A1))
Copy down the column
Missing numbers will be displayed
The column will have empty cells; use Copy followed by Paste Special-Vales
to turn formulas to values
Now you can sort the column to get rid of blanks
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"SiH23" wrote in message
...
I have a list of numbers from 1 to 1000 in Column A. Some of the numbers
are
missing; for example they may run 1, 2 3, 7 etc.

I need a formula that will calculate the missing numbers and place them in
Column B.

Many thanks in advance.