Find Discontinued Number

I have a spredsheet to a few columns and one of the field is check numbers. This column looks like this:
Check #
101
102
103
106
107
109
110
....
120
I want to find out which numbers are missing between 101 and 120. The result I want should be:104, 105,108...
Could anybody help?

Plumstone,

There may be better ways of doing it but try:

Sub tester()
Dim x As Long
Dim y As Long
Dim n As Long
Dim EndCell As Long
Dim StartCell As Long
Dim StartCol As Integer

StartCell = 9
StartCol = 3

EndCell = Cells(Rows.Count, StartCol).End(xlUp).Row
n = StartCell

For x = StartCell + 1 To EndCell
If Cells(x, StartCol).Value Cells(x - 1, StartCol).Value + 1 Then
For y = Cells(x - 1, StartCol).Value + 1 To Cells(x,
StartCol).Value - 1
Cells(n, StartCol + 1).Value = y
n = n + 1
Next y
End If
Next x

End Sub

enter your own row number that the list starts in "StartCell = 9" and your
column number in StartCol = 3.
It places the missing numbers in the adjacent column. If you have other
data under the list then replace *Rows.Count* with a suitable row number.

HTH

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"plumstone" wrote in message
...
I have a spredsheet to a few columns and one of the field is check
numbers. This column looks like this:
Check #
101
102
103
106
107
109
110
...
120
I want to find out which numbers are missing between 101 and 120. The
result I want should be:104, 105,108...
Could anybody help?

Hi!

A simple way of showing the discontinuities in the list:

Assume your list is in col A
In B2 enter =if(A2=1+A1,"","#")
Copy this down as far as your data goes.

If consecutive values in col A do not differ by 1, a # will show.

A second approach would be:

Set up a list of all of the numbers 101 to 120 (eg in G1:G20)
In H1 enter =countif($G$1:$G$20,A1)
Copy down to H20
The 0's will indicate the missing numbers.

If it's a very long list you might want to sort it on col H.

Alf


---
Message posted from http://www.ExcelForum.com/

plumstone

Assuming data in A2:A2000
this array formula will give you a list of the missing numbers:

In B2 enter:

=SMALL(IF(ISERROR(MATCH(ROW($A$1:$A$20)+100,
$A$2:$A$2000,0)),ROW($A$1:$A$20)+100),ROW()-ROW($B$2)+1)

to be entered with ShiftCtrlEnter, also if edited later.

Copy B2 down with the fill handle (the little square in the lower
right corner of the cell).

ROW($A$1:$A$20)+100 calculates the limits, here 101 to 120.


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"plumstone" skrev i en meddelelse
...
I have a spredsheet to a few columns and one of the field is check
numbers. This column looks like this:
Check #
101
102
103
106
107
109
110
...
120
I want to find out which numbers are missing between 101 and 120. The
result I want should be:104, 105,108...
Could anybody help?

Alf,
Thanks. It works.
Plumstone

"AlfD " wrote:

Hi!

A simple way of showing the discontinuities in the list:

Assume your list is in col A
In B2 enter =if(A2=1+A1,"","#")
Copy this down as far as your data goes.

If consecutive values in col A do not differ by 1, a # will show.

A second approach would be:

Set up a list of all of the numbers 101 to 120 (eg in G1:G20)
In H1 enter =countif($G$1:$G$20,A1)
Copy down to H20
The 0's will indicate the missing numbers.

If it's a very long list you might want to sort it on col H.

Alf


---
Message posted from http://www.ExcelForum.com/

Thanks. Although this formus looks so deep to me, it works.
Plumstone

"Leo Heuser" wrote:

plumstone

Assuming data in A2:A2000
this array formula will give you a list of the missing numbers:

In B2 enter:

=SMALL(IF(ISERROR(MATCH(ROW($A$1:$A$20)+100,
$A$2:$A$2000,0)),ROW($A$1:$A$20)+100),ROW()-ROW($B$2)+1)

to be entered with ShiftCtrlEnter, also if edited later.

Copy B2 down with the fill handle (the little square in the lower
right corner of the cell).

ROW($A$1:$A$20)+100 calculates the limits, here 101 to 120.


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"plumstone" skrev i en meddelelse
...
I have a spredsheet to a few columns and one of the field is check
numbers. This column looks like this:
Check #
101
102
103
106
107
109
110
...
120
I want to find out which numbers are missing between 101 and 120. The
result I want should be:104, 105,108...
Could anybody help?

Thanks.

"Sandy Mann" wrote:

Plumstone,

There may be better ways of doing it but try:

Sub tester()
Dim x As Long
Dim y As Long
Dim n As Long
Dim EndCell As Long
Dim StartCell As Long
Dim StartCol As Integer

StartCell = 9
StartCol = 3

EndCell = Cells(Rows.Count, StartCol).End(xlUp).Row
n = StartCell

For x = StartCell + 1 To EndCell
If Cells(x, StartCol).Value Cells(x - 1, StartCol).Value + 1 Then
For y = Cells(x - 1, StartCol).Value + 1 To Cells(x,
StartCol).Value - 1
Cells(n, StartCol + 1).Value = y
n = n + 1
Next y
End If
Next x

End Sub

enter your own row number that the list starts in "StartCell = 9" and your
column number in StartCol = 3.
It places the missing numbers in the adjacent column. If you have other
data under the list then replace *Rows.Count* with a suitable row number.

HTH

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"plumstone" wrote in message
...
I have a spredsheet to a few columns and one of the field is check
numbers. This column looks like this:
Check #
101
102
103
106
107
109
110
...
120
I want to find out which numbers are missing between 101 and 120. The
result I want should be:104, 105,108...
Could anybody help?

You're welcome.
Appreciate the feedback :-)

LeoH


"plumstone" skrev i en meddelelse
...
Thanks. Although this formus looks so deep to me, it works.
Plumstone

Just for drill, of you don't mind all of the numbers in one cell
try this function:

Function lostnumbers(myrange)
startn = myrange(1)
endn = myrange(myrange.Count)
For i = startn To endn
counter = 0
For j = 1 To myrange.Count
If i = myrange(j) Then
counter = counter + 1
Else
End If
Next j
If counter 1 Then
total = total & i & ","
Else
End If
Next i
lostnumbers = Left(total, Len(total) - 1)
End Function

Don
"plumstone" wrote in message
...
I have a spredsheet to a few columns and one of the field is check
numbers. This column looks like this:
Check #
101
102
103
106
107
109
110
...
120
I want to find out which numbers are missing between 101 and 120. The
result I want should be:104, 105,108...
Could anybody help?

"plumstone" wrote...
I have a spredsheet to a few columns and one of the field is
check numbers. This column looks like this:
Check #
101
102
103
106
107
109
110
...
120
I want to find out which numbers are missing between 101 and 120.
The result I want should be:104, 105,108...

Name the range containing the check numbers ChkNumLst and enter the
following array formula in a blank area of the worksheet. I'll enter it in
X99.

X99 [array formula]:
=SMALL(IF(1-COUNTIF(ChkNumLst,ROW(INDIRECT(MIN(ChkNumLst)
&":"&MAX(ChkNumLst)))),ROW(INDIRECT(MIN(ChkNumLst) &":"
&MAX(ChkNumLst)))),ROW()-ROW($X$99)+1)

Select X99 and fill down until the formulas return #NUM!. The resulting list
of missing numbers will be sorted in ascending order, but ChkNumLst needn't
be sorted.