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.

One play ..
In E1: =IF(ISNUMBER(MATCH(ROWS($1:1),D:D,0)),"",ROWS($1:1 ))
In F1: =IF(ROWS($1:1)COUNT(E:E),"",SMALL(E:E,ROWS($1:1)) )
Copy E1:F1 down to F2000 (the last row). The missing numbers will appear in
col F, neatly bunched at the top. Success? hit the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Vic" wrote:
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.

In E1 paste this formula

=IF(ROW(D1)(MAX(D:D)-MIN(D:D)),"Enough",IF(ISERROR(MATCH(MIN(D:D)+ROW(D 1),D:D,FALSE)),MIN(D: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

On Mon, 2 Nov 2009 13:35:01 -0800, Vic
wrote:

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.

Here's another one...

Array entered** in F1:

=SMALL(IF(ISNA(MATCH(ROW(A$1:A$500),D$1:D$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("D1:D2000") '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

--
Biff
Microsoft Excel MVP


"Vic" wrote in message
...
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.

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