# Software

## General discussion

Locked

### Excel - Find the missing numbers

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

Thread display: Collapse - | Expand +

Collapse -

### Excel - Find the missing numbers

by 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 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 In reply to Excel - Find the missing ...

Collapse -

### Excel - Find the missing numbers

by 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 In reply to Excel - Find the missing ...

Collapse -

### Excel - Find the missing numbers

by 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 In reply to Excel - Find the missing ...

Collapse -

### Excel - Find the missing numbers

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

This question was closed by the author

## Related Discussions

• 3

jfrus ·

• 1

#### how to turn off Nero 9 registration reminder

crbillprice ·

• 1

danielbora ·

• 12

#### What is the best AI Chatbot for ecommer website

curiousmuch ·

• 2

hosa ·