Which numbers are missing?

[GWC]

This an example of part of my list:

050000698
050000703

How do I get a list of the numbers that are NOT in that list? For example:

050000699
050000700
050000701
050000702

Note: My entire list contains numbers from 050000698 to 051003040

 

[Auric__]

This an example of part of my list:

050000698
050000703

How do I get a list of the numbers that are NOT in that list? For example:

050000699
050000700
050000701
050000702

Note: My entire list contains numbers from 050000698 to 051003040

Where will the results go? A series of cells, a VBA array, something else?

 

[GS]

Auric__ has brought this to us :

Where will the results go? A series of cells, a VBA array, something else?

I was thinking to dump the list into an array and iterate that for
non-consecutive numbers. When not found consecutive, subtract previous
element from current element and insert into a new 1 dim array. Then
dump the new array back into the wks where specified.

Only thing is making time to do it!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Gary,
Try this and let me know if it does what you want...


Option Explicit

Sub GetMissingNumbers()
Dim n&, k&, x&, vDataIn, vDataOut(), vAns
vDataIn = Range("a1:a12")
For n = LBound(vDataIn) To UBound(vDataIn) - 1
If Not vDataIn(n, 1) + 1 = vDataIn(n + 1, 1) Then
For k = 1 To vDataIn(n + 1, 1) - (vDataIn(n, 1) + 1)
If Not vDataIn(n, 1) + k = vDataIn(n + 1, 1) Then
ReDim Preserve vDataOut(x)
vDataOut(x) = vDataIn(n, 1) + k: x = x + 1
End If
Next 'k
End If
Next 'n
vAns = InputBox("Enter the cell address of where to start putting the
results")
With Range(vAns).Resize(UBound(vDataOut) + 1, 1)
.EntireColumn.ClearContents: .NumberFormat = "0000000000"
.Value = Application.Transpose(vDataOut)
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ron Rosenfeld]

This an example of part of my list:

050000698
050000703

How do I get a list of the numbers that are NOT in that list? For example:

050000699
050000700
050000701
050000702

Note: My entire list contains numbers from 050000698 to 051003040

You can do this with a macro. The macro below assumes that your list starts in A1 and continues down consecutively (no blank rows) as far as required.
It places the results in column B.
Those parameters can be changed if necessary.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

===================================
Option Explicit
Sub MissingNums()
Dim vSrc As Variant, Nums() As Long
Dim vRes() As Variant
Dim rDest As Range
Dim lFirst As Long, lLast As Long
Dim i As Long, j As Long

Set rDest = Range("B1")

'assume values are in column A starting at A1
'If there is a header, or they start at a different number
' change A1 below accordingly
'Also assumes no blanks. If there might be blanks, need to change code below
vSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp))
ReDim Nums(1 To UBound(vSrc, 1))
For i = 1 To UBound(vSrc, 1)
Nums(i) = vSrc(i, 1)
Next i

With WorksheetFunction
lFirst = .Min(Nums)
lLast = .Max(Nums)
ReDim vRes(1 To lLast - lFirst + 1 - UBound(Nums), 1 To 2)
For i = lFirst To lLast
If IsError(Application.Match(i, Nums, 0)) Then
j = j + 1
vRes(j, 1) = i
End If
Next i
End With

Application.ScreenUpdating = False
rDest = "Missing Numbers"
With rDest.Resize(rowsize:=UBound(vRes, 1)).Offset(rowoffset:=1)
.Cells = vRes
.EntireColumn.AutoFit
.NumberFormat = "000000000"
End With
Application.ScreenUpdating = True
End Sub
====================================

 

[Ron Rosenfeld]

You can do this with a macro.

Minor change to clear the column before writing results

=======================================
Option Explicit
Sub MissingNums()
Dim vSrc As Variant, Nums() As Long
Dim vRes() As Variant
Dim rDest As Range
Dim lFirst As Long, lLast As Long
Dim i As Long, j As Long

Set rDest = Range("B1")

'assume values are in column A starting at A1
'If there is a header, or they start at a different number
' change A1 below accordingly
'Also assumes no blanks. If there might be blanks, need to change code below
vSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp))
ReDim Nums(1 To UBound(vSrc, 1))
For i = 1 To UBound(vSrc, 1)
Nums(i) = vSrc(i, 1)
Next i

With WorksheetFunction
lFirst = .Min(Nums)
lLast = .Max(Nums)
ReDim vRes(1 To lLast - lFirst + 1 - UBound(Nums), 1 To 2)
For i = lFirst To lLast
If IsError(Application.Match(i, Nums, 0)) Then
j = j + 1
vRes(j, 1) = i
End If
Next i
End With

Application.ScreenUpdating = False
rDest.EntireColumn.Clear
rDest = "Missing Numbers"
With rDest.Resize(rowsize:=UBound(vRes, 1)).Offset(rowoffset:=1)
.Cells = vRes
.EntireColumn.AutoFit
.NumberFormat = "000000000"
End With
Application.ScreenUpdating = True
End Sub
==============================================

 

[GS]

Ron adds a good idea to autofit after dumping the results...

Sub GetMissingNumbers()
Dim n&, k&, x&, vDataIn, vDataOut(), vAns
vDataIn = Range("a1:a12")
For n = LBound(vDataIn) To UBound(vDataIn) - 1
If Not vDataIn(n, 1) + 1 = vDataIn(n + 1, 1) Then
For k = 1 To vDataIn(n + 1, 1) - (vDataIn(n, 1) + 1)
If Not vDataIn(n, 1) + k = vDataIn(n + 1, 1) Then
ReDim Preserve vDataOut(x)
vDataOut(x) = vDataIn(n, 1) + k: x = x + 1
End If
Next 'k
End If
Next 'n
vAns = InputBox("Enter the cell address of where to start putting the
results")
With Range(vAns).Resize(UBound(vDataOut) + 1, 1)
.EntireColumn.ClearContents: .NumberFormat = "0000000000"
.Value = Application.Transpose(vDataOut)
.Columns(1).AutoFit
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion