Calculating missing numbers

  • Thread starter Thread starter SiH23
  • Start date Start date
S

SiH23

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
 
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
 
Back
Top