Find missing numbers

[Vic]

How do I find missing numbers?
I have numbers from 1 to 500 in columns D1 to D2000 (some numbers may appear
in several cells). However, some numbers don't appear at all. How do I find
missing numbers?
Thnak you.

 

[Gord Dibben]

In E1 paste this formula

=IF(ROW(D1)>(MAX(D)-MIN(D)),"Enough",IF(ISERROR(MATCH(MIN(D)+ROW(D1),D,FALSE)),MIN(D)+ROW(D1),""))

This is an array formula.

Enter with CTRL + SHIFT + Enter.

Double-click to copy down as far as you have data in column D

Or drag/copy down until "Enough" shows up.


Gord Dibben MS Excel MVP

 

[T. Valko]

Here's another one...

Array entered** in F1:

=SMALL(IF(ISNA(MATCH(ROW(A$1:A$500),D$1$2000,0)),ROW(A$1:A$500)),ROWS(F$1:F1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down until you get #NUM! errors meaning all missing numbers have been
returned. This is kind of slow to calculate.

Here's a macro from JMB that will do this. Place the code in a general
module.

Sub FindMissing()
Dim lngUpper As Long
Dim lngLower As Long
Dim i As Long
Dim rngData As Range
Dim lngcount As Long

Set rngData = Range("D12000") 'change as needed
lngLower = 1 'start of sequence
lngUpper = 500 'end of sequence
lngcount = 1

For i = lngLower To lngUpper
If Not IsNumeric(Application.Match(i, _
rngData, 0)) Then
Range("F" & lngcount).Value = i 'sets output to column F
lngcount = lngcount + 1
End If
Next i

End Sub

 

[Max]

Clarification:

Copy E1:F1 down to F2000 (the last row)

should have read as:

Copy E1:F1 down to F500 (copy down by 500 rows)

since you are checking for numbers: 1 - 500 within the source data
The extent of your source data is immaterial
The "copy down by x rows" step is where x = the range of numbers checked
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---