Thread: Identify spare numbers within a fixed range

Thanks for the response JB, very much appreciated....
What I have is:
1. One list of numbers listed sequentially in a column but with gaps where lines have been deleted in the past. These numbers are taken from a live system feed and show the actual numbers being used for that site. The numbers are obviously displayed in an Excel spreadsheet

2. A reference to the beginning and ending of the range the site has been allocated which is not included, or listed within the spreadsheet.

The aim would be to identify these gaps in this number range from the live feed.

Don't really have any experience with MACROs although it does sound like the best option. From what you have already stated it may be easier from my point of view to create a second list in a new column using the full range of numbers allocated.

Only problem is some of these sites have a thousand plus numbers, so I could be back to square 1 having to list all these numbers in a new column.

Mick…
Thanks for this… no idea how much it’s appreciated. This will save me hour of monotonous work. Only problem is it seems to be copying all the numbers, including any missing (gaps) to column E. Is this what’s supposed to happen, or should it just copy the gaps to column E as I’d expeccted? Very possible I’m doing something wrong……….
I’ve had a go at changing the code with no lick obviously….
Thanks again for your time

Hi, Sorry, I Got carried away.!! the Numbers I used for comparison in column "A", were the numbers after the Code , As the codes all seem to be the same.
I've now altered the code, so that It still only uses the numbers after the code, but it read that number from the full number in column "A".
The Results in "E are the missing Numbers,Checked against the numbers after the code in "A") but with code "0191 " added to the results, as you can see from the code.
Hope that fairly clear.
If this code works for you, I could add an Input Box to enter the Range of Numbers to Check, when you run the code, or you could enter them in a seperate cell, for the code to read.

Sub MG22Aug55
Dim Rng As Range, Dn As Range, Bnum As Integer, C As Integer, Chk As Integer
Dim Ray(1 To 601, 1 To 2), nRay, Temp As Double
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
ReDim nRay(1 To 601)
For Bnum = 12200 To 12800
    C = C + 1
    Ray(C, 1) = Bnum
Next Bnum

For Each Dn In Rng
    Temp = Trim(Right(Dn, Len(Dn) - InStr(Dn, " ")))
    For Chk = 1 To UBound(Ray)
        If Temp = Ray(Chk, 1) Then
            Ray(Chk, 2) = Temp
            Exit For
        End If
    Next Chk
 Next Dn
C = 0
For Chk = 1 To UBound(Ray)
    If Ray(Chk, 2) = "" Then
        C = C + 1
        nRay(C) = "0191 " & Ray(Chk, 1)
    End If
Next Chk
 
Range("E1").Resize(C).Value = Application.Transpose(nRay)

End Sub

Regards Mick

MickG…………..
What can I say…..
You’re nothing short of a genius. I’ve spent weeks trying to work out a way of doing this, granted with very little VB\Excel experience. I even downloaded and paid for a couple of Excel add-ons which claimed to be able to run this kind of query, only to find out they couldn’t. An input box would be fantastic just don’t want to push my luck is all.
Will I need to change the area code, range and amount of numbers to check within the code to account for the variations between sites?
Thanks again…………………………..

Hi, Try this:-
Enter Number Search Range Via the code "InputBox" , Format numbers as shown (Complete number range minus Space and Divided by "/").
The result are shown in column "E". Alter range in Last line of code, if Range change required.

Sub MG23Aug06
Dim Rng As Range, Dn As Range, Bnum As Long, C As Integer, Chk As Integer
Dim Ray, nRay, Temp As Double, Dex As Integer, oNum As String
Dim Num1 As Long, Num2 As Long, oNumLg As Long, Fst
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
On Error Resume Next
 oNum = Application.InputBox(prompt:="Insert Search Range" & Chr(10) & "  Format as below:-" _
    & Chr(10) & "   019112200/019112800", _
        Title:="Spare Number Search", Type:=2)
If oNum = "" Or oNum = "False" Then Exit Sub
Fst = Left(oNum, 1)

If MsgBox("The Search Numbers are :-" & Chr(10) & oNum, vbOKCancel _
    + vbQuestion, "Accept/Reject") = vbNo Then Exit Sub

Num1 = Right(Split(oNum, "/")(0), Len(oNum) - 1)
    Num2 = Right(Split(oNum, "/")(1), Len(oNum) - 1)
        oNumLg = Num2 - Num1
            ReDim Ray(1 To Num2 - Num1, 1 To 2)
                ReDim nRay(1 To Num2 - Num1)

For Bnum = Num1 To Num2
    C = C + 1
    Ray(C, 1) = Bnum
Next Bnum

For Each Dn In Rng
    Dex = InStr(Dn, " ") - 2
    Temp = Right(Replace(Dn, " ", ""), Len(Dn) - 1)
    For Chk = 1 To UBound(Ray)
        If Temp = Ray(Chk, 1) Then
            Ray(Chk, 2) = Temp
            Exit For
        End If
    Next Chk
 Next Dn
C = 0
For Chk = 1 To UBound(Ray)
    If Ray(Chk, 2) = "" Then
        C = C + 1
        nRay(C) = Fst & Left(Ray(Chk, 1), Dex) & " " & Right(Ray(Chk, 1), Dex + 2)
    End If
Next Chk
 
Range("E1").Resize(C).Value = Application.Transpose(nRay)

End Sub

Regards Mick

MickG

Can't thank you enough for this. I had a huge contact centre job today, which was ideal for trying out this macro and it worked perfect, saved me bloody hours of work......

Cheers for your time....

I've tried to understand the code but it's probably a bit too advanced for a novice.

Thanks again