Identify spare numbers within a fixed range

**ste001** · 08-19-2009, 11:19 PM

A part of my job requires me to identify spare telephone numbers within a spreadsheet from a fixed range. ie the range allocated may be 0191 12200 to 0191 12800. I then need to physically scroll through 600 numbers picking out any spares from within that spreadsheet.
Is it possible to run a query to identify numbers from the allocated range not contained within a spreadsheet?
And if so can anyone give me any pointers on where to start. As you can imagine this is a pain in the backside part of my job.
Any advice would be greatly appreciated

**JBeaucaire** · 08-19-2009, 11:47 PM

If what we have it two complete lists:
1) A list of all the phone numbers from 0191 12200 to 0191 12800 listed sequentially and completely in a column
2) A list of used phone numbers in another column

If that's what we have, this can be done very easily with formula, or conditional formatting to cause the numbers in the FULL list (1) to light up if they are NOT in the second list.
=======

If what we have is:
1) A list of used telephone numbers in a column
2) No second list, just a reference to the beginning and ending of the range (0191 12200 to 0191 12800), then I would guess you would need a macro to loop through all the possible numbers in the range and check if they are in the list (1), IF NOT...then drop those unused numbers into a new list in another column.

So, what do we have here?

**ste001** · 08-20-2009, 12:19 AM

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.

**MickG** · 08-20-2009, 07:49 AM

Hi, This code assumes your List of used numbers are in column "A".
The code first creates an 2 dimension Array (2 columns) all the numbers, using the last set of digits. i.e. 12200 to 12800. The code Placing all these numbers in the first column of the Array.
The code then runs through all the used numbers in column "A", checking them against the previously created Array of all numbers.
If it finds a duplicate it Places the number in column (2) of the first array, against the duplicate number in column (1) of the array.
The final loop runs through the, now modified First Array and checks for Blank spaces, these are the numbers in column (1) that have not been used.
If a Blank space is found the number in colunm (1) is places in a new array "nRay" and finally Insert in column "E".
NB:- I did it this way because I thought it would be quicker, if you have a larger set of numbers.
Hope this helps

Sub MG20Aug19
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
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
    For Chk = 1 To UBound(Ray)
        If Dn = Ray(Chk, 1) Then
            Ray(Chk, 2) = Dn
            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

**ste001** · 08-21-2009, 04:51 PM

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

**MickG** · 08-22-2009, 06:18 AM

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