Calculating missing numbers

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.
 
B

Bernie Deitrick

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
 
B

Bernard Liengme

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to count the numbers in a Word document? 0
Word- Find and Replace 1
Found few bugs in my daily routine of work in excel for MAC 0
Setting column width in a table with a macro is not working 0
Excel does not accept text format! 0
How can the font and size of (all) citation numbers be altered? 1
Found few bugs in my daily routine of work in excel for MAC 0
Finding the right style field in VBA 0

Top