Findinf missing values in a sequential list

[Chris Strug]

Hi,

Pretty much as the title says. I have a sheet containing several columns of
data. One of these columns is a numeric reference that is *meant* to run
sequentially - however, there tends to be the odd value missing.

Simply, how do I take a range of values and find out the missing numbers?

For example, given:

1
2
3
6
8
9
10
11
12
13
14
20

I would like to see:
4, 5, 7, 15, 16, 17, 18, 19

I've been racking my brains but I'm stuck. Can anyone help?

Any advice gratefully recieved.

Thanks

Chris.

[Bernie Deitrick]

Chris,

Here's a macro that will show missing numbers: it shows the missing numbers in a message box. It may get a bit messy if you have a
lot of numbers, but they'll be there. Select a single cell in your column of numbers prior to running: I asumed that the only
numbers in the column are the numbers of interest.

HTH,
Bernie

Sub ShowMissingNumbers()
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

"Chris Strug" <solac...@SOSPAMhotmail.com> wrote in message news:eDHmBrO...@TK2MSFTNGP10.phx.gbl...

[Vaskor Basak]

One way might be to create another column with the
complete range of values (by typing "1" in the top
cell, "2" in the one below and dragging down). Then use
VLOOKUP to search for the values on your original list.
For example, if your list range is from cell A2 to A80 and
the newly created complete list range is from C2 to C100,
then in D2 for example, type "=VLOOKUP(C2,A$2:A$80,1,0)"
and drag the formula down. Any items not found will
result in a result of "#N/A".

I hope this is helpful.

>.
>

[Harlan Grove]

"Chris Strug" wrote...

>Pretty much as the title says. I have a sheet containing several columns of
>data. One of these columns is a numeric reference that is *meant* to run
>sequentially - however, there tends to be the odd value missing.
>
>Simply, how do I take a range of values and find out the missing numbers?
>
>For example, given:
>
>1
>2
>3
>6
>8
>9
>10
>11
>12
>13
>14
>20
>
>I would like to see:
>4, 5, 7, 15, 16, 17, 18, 19

..

If these values would never be less than 1 or greater than 65536, you could try
the following. If your original range were named LST, in another range, say,
beginning in K3, enter the array formula

=IF(MAX(LST)-MIN(LST)>ROWS(LST)-1,
MATCH(0,COUNTIF(LST,ROW(INDIRECT((MIN(LST)+1)&":"&(MAX(LST)-1)))),0)+MIN(LST),
"")

This will give the lowest missing value if there is one, or "" if there are no
missing values. In the cell below it (K4), enter the array formula

=IF(MAX(K$3:K3)+IF(ISNUMBER(K$3),1,1E+300)<MAX(LST),
MATCH(0,COUNTIF(LST,ROW(INDIRECT((K3+1)&":"&(MAX(LST)-1)))),0)+K3,"")

This will give the next missing value if there is one, or "" if there isn't.
Fill this formula down as far as needed.

--
Public Service Announcements:
1. Don't attach files to postings in this newsgroup.
2. Learn how to snip unnecessary text from quoted material. Indiscriminate
quoting wastes more bandwidth than file attachments.

[Chris Strug]

All,

Just a quick note to say thanks for your replies. I'm going through them now
and I'm sure that at least one of them will find their way into my archive!

Cheers

CS