LinkBack URL
About LinkBacks
Hi
I have an ordered list of 2580 IP addresses that are in use in 18 /24 subnets (total of 4608 addresses). I want to inverse this list and get the list of IP addresses that are not in use.
Anyone has an idea?
Hi
I have an ordered list of 2580 IP addresses that are in use in 18 /24 subnets (total of 4608 addresses). I want to inverse this list and get the list of IP addresses that are not in use.
Anyone has an idea?
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).
Cheers!
Deep Dave
Thanks for your quick reply, I have attached a sample workbook.
BEFORE: List of IP addresses in use in the 192.168.1.0/24 subnet
AFTER: List of IP addresses NOT in use in that subnet.
Are you fine with VBA?
Yes, I can do some VBA.
b2=RIGHT(A2,LEN(A2)-10)*1
c2=IF(ISNA(VLookup(Row();$b$2:$b$15000;1;0)="true"),"192.168.1."&Row(),"")
see the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
See the file attached.
Please Login or Register to view this content.
Thank you both for your reply. I have been fiddling around but don't quite get the result.
The worklist is actually more like the one in this attachment. There are 18 different subnets of 256 IP addresses each.
Oeldere, in your case the last missing addresses are not shown. e.g. 192.168.1.158
Oeldere, in your case the last missing addresses are not shown. e.g. 192.168.1.158
expand the formula in column C till row 254 (since this is the last value in column A.
see the attached file.
Last edited by oeldere; 04-18-2016 at 09:19 AM. Reason: red text added
Try this formula in B2:
=IFERROR("192,168.1,"&SMALL(IF(COUNTIF($A$2:$A$157,"192.168.1."&ROW($1:$254))=0,ROW($1:$254),""),ROW(A1)),"")
Enter with Ctrl+Shift+Enter
What is incorrect with my solution? It returns exactly the same result as expected.Originally Posted by tizzen33
Did you run the code by clicking on the run button?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks