In an excel sheet i have numbers in A column. Suppose, in column A the numbers are as follows:
1
2
3
7
8
11
12
13

I need a formula in excel to find out only those numbers which are missing in the list. For example, in the above list, 4,5,6,9,10 numbers are missing.


Yogesh

__________________
Yogesh

Are the numbers always going to be in sequential order?

Assuming they will, this should work:

--------------------------------------------------------------
'Checks column A for missing numbers, putting them in cell B1 separated by columns.
  Dim iRowOn As Long, iNumber As Long, sMissing As String
  iRowOn = 1 'Or first cell with number. Will think it's done at first blank line
  iNumber = 1
  Do While Cells(iRowOn, 1).Value <> ""
    If iNumber < Cells(iRowOn, 1).Value Then
      Do While iNumber < Cells(iRowOn, 1).Value
        sMissing = sMissing & ", " & iNumber
        iNumber = iNumber + 1
      Loop
    End If
    iRowOn = iRowOn + 1
  Loop
  Cells(1, 2).Value = Right(sMissing, Len(SMissing) - 2)
--------------------------------------------------------------

This is rough but should do what you want given that the numbers are sorted with no blank rows between numbers.

Hope this helped point you in the right direction.

Thanks, I will try in vba, but i just want a excel formula for that.

Yogesh