Discussion Locked

Excel - Find the missing numbers

0 helpful
8 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a sequence of numbers with a start range of 1 to 10,000.

In this column of 1 - 10,000 the numbers are sorted sequentially but in between some of the numbers are missing.

For example between 1 - 10,000, the numbers missing are 5, 120, 3000, 4500 and various other numbers. How do I build an excel formula to tell me what numbers are missing in this sequence.

I know there is a way to produce it using the VB code by using a FOR-NEXT-LOOP but is there a way to find the answer with using VB coding.

8 replies

by don christner

My answer is based on Excel97, so if you are using a different version, you may have to adjust this answer.

First insert a column next to the column that contains the 1-10,000. In the inserted column fill in the numbers 1-10,000.

Next, select both columns, click on the EDIT menu, then click on GOTO, then click on SPECIAL. Finally, click on ROW DIFFERANCES. This will bring you to cells that do not match.

Don

by tclere

Try this:

Sub FindMissingNumbers()
Dim iIdx As Long '1-10000
Dim iRow As Long
Dim iVal As Long
Dim sList As String

iRow = 1 'First row of data

For iIdx = 1 To 10
iVal = Range("A" & iRow).Value
If iVal > iIdx Then
'missing value
sList = sList & iIdx & vbCrLf
Else 'found it
iRow = iRow + 1
End If
Next iIdx

'Display the missing numbers
MsgBox sList
End Sub

It will show you a list of missing numbers. You may have to tweak the line iRow = 1 to reflect the row number that contains the first index (1). You may also have to change the Range("A" ..) statements to reflect the column that containsthe index numbers.

Hope this helps!

by parier

I have a few answers, I can email you the xls file, if interested, reply to this email with a copy of this problem

eparisek@mtga.com

This discussion is closed to new replies.