Finding missing numbers from a list

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

 

[Earl Kiosterud]

Note. This has been multiposted. It's also in public.excel.

 

[Peo Sjoblom]

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

 

[Otis Wengatz]

Note. This has been multiposted. It's also in public.excel.

cross posted, not multiposted.

 

[Otis Wengatz]

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

Many thanks for the reply!

 

[Otis Wengatz]

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

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.

 

[Bernie Deitrick]

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 Wengatz]

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

Aha, that's it.

thank you very much!

--

 

[Don Guillett]

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

 

[immanuel]

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.

 

[Max]

Nice, neat formula!

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

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