Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

My company uses a giant excel spreadsheet that shows which IP Addresses are being used by which computers (among other information). IP Addresses that are not in use still have a row, but the computer name field will be empty.

Lets say, for example, that my spreadsheet might look like this:

Computer Name  |  IP Address  
Joe-PC         |  
Tom-PC         |  
               |  <----- This IP is not used
Scott-PC       |

I would like to create a list of all the IP addresses that are currently not in use. So, I need to search for all the rows where "A" is empty, and then add "B" to the list. Is there a way to do this within excel?

share|improve this question
Please edit your question with the version of Excel you are using. This will help get a more precise answer to your question. – CharlieRB Feb 8 '12 at 16:11
up vote 2 down vote accepted

In 2010 make a insert a table and check the "My table has headers" box. It should have selected all the data. If not, enter the correct range then click OK.

Once the table is created, click the filter button on the "Computer Name" header. Unselect the check box for "Select All" and scroll to the bottom and select "Blank". Click OK and it will only show you the rows without a computer name.

enter image description here

Once you have this data, if you want to transfer it to another column/sheet/workbook, select your range and hit alt+; to select only visible cells and hit ctrl+c to copy the selected visible cells.

If you are using a previous version, these specific instructions may not work.

share|improve this answer
This works in 2007 and in 2003 it's called a list and still works. – Jesse Feb 8 '12 at 17:20
+1 You are absolutely correct. I believe the way to accomplish it is quite different than 2010 though. – CharlieRB Feb 8 '12 at 17:58

This macro will paste your list in column C

Sub AutofilterBlanks()`

    ActiveSheet.Range("A:B").AutoFilter Field:=1, Criteria1:="="
    Application.CutCopyMode = False

End Sub
share|improve this answer

You can create the list with an array formula:


Enter by pressing Ctrl+Shift+Enter. Then, fill down as far as you need to.

enter image description here

It's worth noting that this is a dynamic solution; when your table is updated, the list of available IP addresses will update too with no user input. Just make sure your formula is filled down sufficiently far.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .