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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Regards Mick

**ste001** · 08-22-2009, 11:27 AM

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

**MickG** · 08-23-2009, 07:11 AM

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.

Please Login or Register  to view this content.

Regards Mick

**ste001** · 08-25-2009, 03:18 PM

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