Finding missing numbers from a list

  • Thread starter Thread starter Otis Wengatz
  • Start date Start date
O

Otis Wengatz

I'm using Excel 2000.

I have a list of number, from 0000 to 1000, sorted from smallest to
largest.

But the list is not complete, there are gaps in the number sequence, eg:

0000
0025
0026
0027
0028
0045

etc.

Whats the easiest way to create another list of the missing numbers? EG,
using the example above

0001
0002
0003

etc.

Very many thanks.
 
Format cell as 0000, in the same cell put 1

hold down ctrl key while dragging the cell with 1 to copy it down 1000 rows,
copy it and paste them over your other numbers..

If you want text, in the first cell use

=TEXT(ROW(1:1),"0000")

copy down 1000 rows and paste special as values over the old numbers
 
Format cell as 0000, in the same cell put 1

hold down ctrl key while dragging the cell with 1 to copy it down 1000
rows, copy it and paste them over your other numbers..

If you want text, in the first cell use

=TEXT(ROW(1:1),"0000")

copy down 1000 rows and paste special as values over the old numbers
Click to expand...

Many thanks for the reply!
 
Format cell as 0000, in the same cell put 1

hold down ctrl key while dragging the cell with 1 to copy it down 1000
rows, copy it and paste them over your other numbers..

If you want text, in the first cell use

=TEXT(ROW(1:1),"0000")

copy down 1000 rows and paste special as values over the old numbers
Click to expand...

Eh? That just replaces my list with a list of all numbers, which isn't
what I want at all.

What I've got:
1
3
4
5
6
8


What I want:
2
7
9

What I do not want
1
2
3
4
5
6
7
8
9

unless 2,7,9 have special formatting making them easy to find.

Thanks very much for any advice.
 
Otis,

Assume: Your numbers are in column A, blank columns for B and C.

In Cell B1, enter '0001
In Cell B2, Enter '0002

Select both cells, grab the fill handle and drag down to row 1000.

In cell C1, use the formula
=ISERROR(MATCH(B1,A:A,FALSE))

Then copy down for 1000 rows.

Sort B:B on column C, and delete any values in column B where column C
is False. Then you have all your values that aren't present in column
A.

HTH,
Bernie
MS Excel MVP
 
Otis,

Assume: Your numbers are in column A, blank columns for B and C.

In Cell B1, enter '0001
In Cell B2, Enter '0002

Select both cells, grab the fill handle and drag down to row 1000.

In cell C1, use the formula
=ISERROR(MATCH(B1,A:A,FALSE))

Then copy down for 1000 rows.

Sort B:B on column C, and delete any values in column B where column C
is False. Then you have all your values that aren't present in column
A.

HTH,
Bernie
MS Excel MVP
Click to expand...
Aha, that's it.

thank you very much!

--
 
Sub ShowMissingNumbers()'Bernie Deitrick
Dim i As Long
Dim myMissing As String
myMissing = "Blank"
For i = Application.Min(ActiveCell.EntireColumn) To _
Application.Max(ActiveCell.EntireColumn)
If ActiveCell.EntireColumn.Find(i, , xlValues, xlWhole) Is Nothing Then
If myMissing = "Blank" Then
myMissing = "Missing Numbers are:" & Chr(10) & i
Else
myMissing = myMissing & ", " & i
End If
End If
Next i
MsgBox myMissing
End Sub
 
Just for fun. (I'm aware that your issue has been resolved by a neat post by
Bernie.)

Assuming your list of incomplete data is numeric and begins in A1.

=IF(SMALL(IF(ISERROR(MATCH(ROW(INDIRECT("1:1000")),$A$1:$A$1000,0)),ROW(INDI
RECT("1:1000")),1001),ROW())<1001,SMALL(IF(ISERROR(MATCH(ROW(INDIRECT("1:100
0")),$A$1:$A$1000,0)),ROW(INDIRECT("1:1000")),1001),ROW()),"")

(Remember to enter the formula by hitting Ctrl-Shift-Enter instead of Enter
after pasting it into B1.)

Drag it down to B1000 et voila.

/i.
 
Nice, neat formula!

cheers
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email
-------------------------------------------
 
Back
Top