Find Discontinued Number

[Guest]

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?

 

[Sandy Mann]

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

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

 

[AlfD]

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.

Al

 

[Leo Heuser]

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 <Shift><Ctrl><Enter>, 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.

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

 

[Guest]

Alf,
Thanks. It works.
Plumstone

 

[Guest]

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

 

[Guest]

Thanks.

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



numbers. This column looks like this:
result I want should be:104, 105,108...

 

[Leo Heuser]

You're welcome.
Appreciate the feedback

LeoH

 

[Don]

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

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

 

[Harlan Grove]

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.

 

[Leo Heuser]

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.

What's the advantage of using your formula with 13 function calls
compared to my formula from yesterday with 8 calls?

LeoH

 

[Harlan Grove]

"Harlan Grove" <[email protected]> skrev i en meddelelse ....

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)

....
What's the advantage of using your formula with 13 function calls
compared to my formula from yesterday with 8 calls?

For reference, your formula:

=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)

So, you mean aside from the fact that yours hardcodes the range of numbers
to be checked while mine adapts itself to 1..65536? If I were to follow your
lead (partially),

X99 [still and array formula]:
=SMALL(IF(COUNTIF(ChkNumLst,ROW($A$101:$A$120))=0,
ROW($A$101:$A$120),ROW()-ROW($X$99)+1)

Now just 7 function calls. So at this point, what's the advantage of yours?
If you're checking exact equality (or inequality) of numbers, no need to use
MATCH and the obligatory enclosing ISERROR. A single COUNTIF suffices.

The ROW(INDIRECT(MIN(ChkNumLst)&":"&MAX(ChkNumLst))) term in my original
formula adapts itself to the data in ChkNumLst. I consider that a compelling
advantage in the general case. If not, my COUNTIF formula with hardcoding
would seem to be optimal.

Any more questions?

 

[Leo Heuser]

"Harlan Grove" <[email protected]> skrev i en meddelelse ...

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)

...
What's the advantage of using your formula with 13 function calls
compared to my formula from yesterday with 8 calls?

For reference, your formula:

=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)

So, you mean aside from the fact that yours hardcodes the range of numbers
to be checked while mine adapts itself to 1..65536?

You consider it an advantage, that your formula is limited to 1..65535?
I discarded that in one of my previous attempts in favor of a broader range.
My formula only has the limit, that the difference between the start and end
number must not exceed 65534. If for example the numbers to check were
120001 to 150000, I don't see how to use your formula to solve that, while
my formula would do it with ROW($A$1:$A$30000)+120000.

If I were to follow your
lead (partially),

X99 [still and array formula]:
=SMALL(IF(COUNTIF(ChkNumLst,ROW($A$101:$A$120))=0,
ROW($A$101:$A$120),ROW()-ROW($X$99)+1)

Now just 7 function calls. So at this point, what's the advantage of yours?
If you're checking exact equality (or inequality) of numbers, no need to use
MATCH and the obligatory enclosing ISERROR. A single COUNTIF suffices.

I haven't said anything about my formula having an advantage
compared to this formula. How could I, since you have just introduced it,
but MATCH/ISERROR has the advantage, that it can check an array,
which cannot be accomplished with COUNTIF (unfortunately).

LeoH

 

[Soo Cheon Jheong]

Plumstone,

1) Create the following worksheet
-------------------------------------------------------
A B C D E
1 Check # Missing # Min # Max #
2 101 (Formula) (Value) (Value)
3 102 .
4 103 .
5 106 .
6 107
7 109
8 110
9 .
10 .
11 .
12
13
14
15
16
17
18
19 . .
20 . .
21 . .
-------------------------------------------------------

2) Enter a formula as array formula in B2:

=IF(OR($D$2="",$E$2="",$D$2>$E$2,ROWS(B$2:B2)>$E$2-$D$2+1
-COUNTIF($A$2:$A$21,">="&$D$2)+COUNTIF($A$2:$A$21,">"&$E$2)),
"",SMALL(IF(COUNTIF($A$2:$A$21,ROW(INDIRECT("1:"&
ROWS($A$2:$A$21)))+$D$2-1)=0,ROW(INDIRECT("1:"&
ROWS($A$2:$A$21)))+$D$2-1,""),ROW()-ROW(B$2)+1))

then drag and fill down.


3) Enter a value in D2.

e.g. 101 or 102 or 103, ...

4) Enter a value in E2.

e.g. 120 or 119 or 118, ...


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--

 

[Harlan Grove]

You consider it an advantage, that your formula is limited to 1..65535?

....

No, and good point. Make that subexpression

MIN(ChkNumLst)+ROW(INDIRECT("1:"&(MAX(ChkNumLst)-MIN(ChkNumLst))))

which can handle ranges with arbitrary starting points but still limited to
a range of 65536 from the smallest. Also note that it's guaranteed that the
MIN value is in the range, so no reason to check it, so the range to be
checked may start with the next value after the MIN. By the same argument,
the MAX value is also guaranteed to be in the range, but it's best to leave
it in the check to avoid potential #REF! errors when the range contains only
two adjacent numbers.

Getting fancier, it's possible to use ROW and COLUMN calls to handle ranges
that exceed 65536 values between MIN and MAX.

MIN(ChkNumLst)+ROW(INDIRECT("1:"&MIN(MAX(ChkNumLst)-MIN(ChkNumLst),
65535)))+65535*(COLUMN(INDIRECT("RC1:RC"&ROUNDUP((MAX(ChkNumLst)
-MIN(ChkNumLst))/65535,0),0))-1)

This can produce huge arrays that can really slow Excel down, but this
approach *can* be used to go beyond the n..n+65536 range limit.

. . . If for example the numbers to check were
120001 to 150000, I don't see how to use your formula to solve that,
while my formula would do it with ROW($A$1:$A$30000)+120000.

Believe I've now explained that mystery for you.

but MATCH/ISERROR has the advantage, that it can check an array,
which cannot be accomplished with COUNTIF (unfortunately).

To be fair, MATCH has the advantage that it stops searching once a match has
been found. However, if the range were sorted in ascending order, and if the
values were numeric, then nothing beats

LOOKUP(MIN(ChkNumLst)+ROW(INDIRECT("1:"&(MAX(ChkNumLst)
-MIN(ChkNumLst)))),ChkNumLst)=MIN(ChkNumLst)
+ROW(INDIRECT("1:"&(MAX(ChkNumLst)-MIN(ChkNumLst))))

in terms of speed since LOOKUP would use binary search on each value in
ChkNumLst. Benchmark it if you don't believe me.

 

[Harlan Grove]

...
...

LOOKUP(MIN(ChkNumLst)+ROW(INDIRECT("1:"&(MAX(ChkNumLst)
-MIN(ChkNumLst)))),ChkNumLst)=MIN(ChkNumLst)
+ROW(INDIRECT("1:"&(MAX(ChkNumLst)-MIN(ChkNumLst))))

in terms of speed since LOOKUP would use binary search on each value in
ChkNumLst. Benchmark it if you don't believe me.

Better still, if the topmost result were in X99,

X99 [array formula]:
=MIN(IF(LOOKUP(MIN(ChkNumLst)+ROW(INDIRECT("1:"&(MAX(ChkNumLst)
-MIN(ChkNumLst)))),ChkNumLst)<>MIN(ChkNumLst)+ROW(INDIRECT("1:"&
(MAX(ChkNumLst)-MIN(ChkNumLst)))),MIN(ChkNumLst)+ROW(INDIRECT("1:"&
(MAX(ChkNumLst)-MIN(ChkNumLst))))))

X100 [array formula]:
=MIN(IF(LOOKUP(X99+ROW(INDIRECT("1:"&(MAX(ChkNumLst)-X99))),ChkNumLst)
<>X99+ROW(INDIRECT("1:"&(MAX(ChkNumLst)-X99))),X99+ROW(INDIRECT("1:"&
(MAX(ChkNumLst)-X99)))))

Then select X100 and fill down as needed.

 

[AlfD]

Hi!

I think probably Harlan 2 Leo 1 (who am I to judge?)
But Plumstone must be fascinated by the maelstrom he s/he ha
unleashed!

Al

 

[Sandy Mann]

Hi!

I think probably Harlan 2 Leo 1

only after extra injury time! <g>

Sandy

 

[Leo Heuser]

...

Getting fancier, it's possible to use ROW and COLUMN calls to handle
ranges that exceed 65536 values between MIN and MAX.

MIN(ChkNumLst)+ROW(INDIRECT("1:"&MIN(MAX(ChkNumLst)-MIN(ChkNumLst),
65535)))+65535*(COLUMN(INDIRECT("RC1:RC"&ROUNDUP((MAX(ChkNumLst)
-MIN(ChkNumLst))/65535,0),0))-1)

This can produce huge arrays that can really slow Excel down, but this
approach *can* be used to go beyond the n..n+65536 range limit.

Assuming ChkNumLst contains the numbers 1,2,3,66000 (in e.g. D14), I take
it, that your formula is supposed to return the array {4,5,6,,,,,65999}?

If my assumption is correct, I would expect

=INDEX(MIN(ChkNumLst)+ROW(INDIRECT("1:"&MIN(MAX(ChkNumLst)-MIN(ChkNumLst),
65535)))+65535*(COLUMN(INDIRECT("RC1:RC"&ROUNDUP((MAX(ChkNumLst)
-MIN(ChkNumLst))/65535,0),0))-1),1)

to return 4, but it returns 2 !?

How would you put the returned array into the worksheet?

To be fair, MATCH has the advantage that it stops searching once a match
has been found.

To be precise, my answer

was to your statement:

Quote:

"If you're checking exact equality (or inequality) of numbers, no need to
use MATCH and the obligatory enclosing ISERROR. A single COUNTIF
suffices."

End quote.

I tried to point out, that COUNTIF doesn't work on arrays.

However, if the range were sorted in ascending order, and if the
values were numeric, then nothing beats

LOOKUP(MIN(ChkNumLst)+ROW(INDIRECT("1:"&(MAX(ChkNumLst)
-MIN(ChkNumLst)))),ChkNumLst)=MIN(ChkNumLst)
+ROW(INDIRECT("1:"&(MAX(ChkNumLst)-MIN(ChkNumLst))))

in terms of speed since LOOKUP would use binary search on each value in
ChkNumLst. Benchmark it if you don't believe me.

I share your enthusiasm about the LOOKUP function. Too bad, that the range
has to be sorted, or to put it another way: it would be nice, if VLOOKUP and
HLOOKUP could take a vector as their first argument.

LeoH

 

[Harlan Grove]

Assuming ChkNumLst contains the numbers 1,2,3,66000 (in e.g. D14),
I take it, that your formula is supposed to return the array
{4,5,6,,,,,65999}?

No, it should return {2,65537;3,65538;4,65539;...;65536,131071}.

If my assumption is correct, I would expect

=INDEX(MIN(ChkNumLst)+ROW(INDIRECT("1:"&MIN(MAX(ChkNumLst)
-MIN(ChkNumLst),65535)))+65535*(COLUMN(INDIRECT("RC1:RC"
&ROUNDUP((MAX(ChkNumLst)-MIN(ChkNumLst))/65535,0),0))-1),1)

to return 4, but it returns 2 !?

If you misunderstand how my expression works, don't be surprised that you
fail to anticipate what it returns.

How would you put the returned array into the worksheet?

....

Um, the normal way, but it's not supposed to be directly entered in a
worksheet. It's meant to be fed to SMALL. Note: you can select
R1C1:R65535C50 and enter the array formula

=ROW(INDIRECT("1:65535"))+65535*(COLUMN(INDIRECT("A:AX"))-1)

and it'll populate the range with sequential integers from 1 to 50*65535.
Can you not do this in Excel on your PC?

I tried to point out, that COUNTIF doesn't work on arrays.

OK, you're right. COUNTIF doesn't work with arrays. If the array in question
(smallarray) is sorted, however,

LOOKUP(bigarray,smallarray)=bigarray

is more efficient than ISNA(MATCH(bigarray,smallarray,0)).

I share your enthusiasm about the LOOKUP function. Too bad, that the
range has to be sorted, or to put it another way: it would be nice,
if VLOOKUP and HLOOKUP could take a vector as their first argument.

Well, it's an assumption, but the OP's array did appear sorted, and some
data processing experience would show that finding gaps in sequences is much
easier when the list is sorted. So for this sort of application, assuming
the list is sorted is a hint that the list should be sorted.

As for VLOOKUP and HLOOKUP, given the following table in A1:B20

A 3
D 5
E 53
G 77
G 87
H 15
L 12
L 12
L 62
M 36
O 58
O 75
P 83
R 31
S 41
T 57
U 4
V 35
Y 64
Z 15

the formula

=VLOOKUP({"A","E","I","O","U"},A1:B20,2)

if entered into a 5 column by 1 row range returns

3 53 15 75 4

And similarly for HLOOKUP. It's not that these functions can't take array
1st arguments, the problem is that they don't return arrays when fed such
1st arguments. They return something akin to what INDEX returns when fed
array 2nd or 3rd arguments.