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.
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...
I hope this is helpful.
>.
>
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.
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