General discussion

Locked

Excel - Find the missing numbers

By im_chaz ·
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.

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel - Find the missing numbers

by Don Christner In reply to Excel - Find the missing ...

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

Collapse -

Excel - Find the missing numbers

by Don Christner In reply to Excel - Find the missing ...

P.S. When you are done making corrections, just delete the column that you inserted.

Collapse -

Excel - Find the missing numbers

by im_chaz In reply to Excel - Find the missing ...

Poster rated this answer

Collapse -

Excel - Find the missing numbers

by tclere In reply to Excel - Find the missing ...

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!

Collapse -

Excel - Find the missing numbers

by im_chaz In reply to Excel - Find the missing ...

Poster rated this answer

Collapse -

Excel - Find the missing numbers

by parier In reply to Excel - Find the missing ...

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

Collapse -

Excel - Find the missing numbers

by im_chaz In reply to Excel - Find the missing ...

Poster rated this answer

Collapse -

Excel - Find the missing numbers

by im_chaz In reply to Excel - Find the missing ...

This question was closed by the author

Back to Software Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums