EXCEL 2007/03: Formula for numbers NOT listed?

Question

I have a spreadsheet which contains about 1,000 rows of somewhat contiguous numbers. They are phone extensions that are in use. I would like to know which extensions are not in use. Is there a formula I can apply that shows all the numbers that are NOT displayed on the spreadsheet, within a certain range. (valid extensions are 3000 through 5000, and 8000 through 9000)

Answer 1

When you say 'somewhat contiguous', I infer you mean they are not all in one column.  

The following macro can handle that, but you will have to adjust the ranges to fit the data locations in your worksheet.

In my example, the macro will populate cells F1 to F3002 with ALL valid extension numbers you indicate, in sequence.

Then it will compare each cell in F1:F3002 with all cells in the range A1 to E1100.  When it finds a match, it will delete the entry in Column F.

At the end of the macro run, Column F will contain only AVAILABLE extension numbers, in sequence.

If your data is in Columns A thru J, then change Line 4 to read "A1:J1100" and Line 5 to "K1:K3002", or as your Column situation dictates.

Then, open your workbook

Copy the adjusted macro to the clipboard:

Sub List_Available_Ext()

Dim rng As Range

Dim rng2 As Range

Set rng = Range("A1:E1100")

Set rng2 = Range("F1:F3002")

Dim x, y

x = 3000

y = 8000

Application.ScreenUpdating = False

For Each cell In rng2

cell.Select

If x <= 5000 Then

   cell.Value = x

x = x + 1

Else

cell.Value = y

y = y + 1

End If

Next cell

For Each cell In rng2

cell.Select

If Application.WorksheetFunction. _

CountIf(rng, ActiveCell) > 0 Then

ActiveCell.Delete

End If

Next

Application.ScreenUpdating = True

End Sub

Next, press ALT + F11

Insert  >  Module

Paste the macro into the Module area to the right.

Close back to Excel.

Go to Tools  >  Macro  >  Macros

Click on this macro to highlight it, if it is not already highlighted.

Click:  Options

Select a letter to be used as a keyboard shortcut.

Close back to Excel.

Press CTRL + your shortcut letter to run the macro.  Based on having to check 3000 entries against many columns, it will take about 3-4 seconds for the macro to run.  At the end you should have the data you requested.

Answer 2

one way to do this (i assume you know how to use fill):

- open up a new spreadsheet to test

- copy&paste your 1000 rows of used numbers into column A starting cell A1

- in B1 type 3000

- in B2 type 3001

- then highlight the 2 and use the fill handle to fill in to 5000

- then do the same thing at the end of the list in column B for 8000 to 9000.

- then in cell C1 use this formula

=IF(COUNTIF(A:A,B1)>0, "used","Available")

then copy&paste (or fill) the formula down the C column.  this will show you the available extensions.

- then you can use auto-filter or just sort by column C

- and B should have your list of available extensions at the top

- personally, i would replace the "used" with a vlookup() so you have the names of people with the extensions on the same page....its up to you.

good luck.

edit-

oops

by highlight the 2...i meant "highlight both B1 and B2" (lol it would have made more sense if my above assumption about knowing the fill was correct...but anyway)

- if you have both cells highlighted...then click the fill dot (bottom right corner of highlighted cell) and drag down

- an even easier way...is to type 3000 in B1

- then right click on the fill dot and drag the highlight down.

- when you let go, you will be given the option to "fill series"

i dont think you want to do the =b1+1 formula...because when you sort...its going to foul up your numbers

i've had to make a very very similar spreadsheet