IP Range Question

[Chris_Rodgers]

Hi everyone, 

       Got a project that is pretty daunting but I think it can be pretty cool. I am taking all my company's IP ranges and creating a database that maps out data centers and other important topics. Here is my question:

My current program that I have runs with IP ranges and I have separated the ranges into 4 different levels. If the IP is 10.20.30.40

Level 1 - 10

Level 2 - 10.20

Level 3 - 10.20.30

Level 4 - 10.20.30.40

Rules for this is that everything in level 2-4 goes from 0-255 and I will seldom use level 1 by itself.

The range in this situation may be 10.20.30.40 - 10.20.30.255 for Boston / 10.20.50.00 - 10.20.80.255 for Austin

Here is my issue. I have close to half a million possible IP's and each range is associated with a data center. I am happy to make a .yxdb database but I would love to utilize a tool that I know exists, but the multi row formula seems like the wrong choice. I am leaning towards a find and replace tool but It would be great to find out what you all have done.

I have thought about setting up a listing of lookup where it goes from most granular to least granular 

Level 3 Strings to look up ranges in the level 3 range for Austin as the range goes from 10.20.50 - 10.20.80 so anything within that range will be in Austin

Level 4 String to look up exact IP's in the level 4 range for Boston as the range goes from the 4th level .40-.255 as anything within that range will be in Boston

I have roughly 300 ranges that exist and I can look though everything to determine ranges but it would be really nice to get some outside perspective. Any thoughts? 

[patrick_digan]

@Chris_Rodgers Fun challenge! What if you converted the IP addresses to numbers and then used the dynamic replace to efficiently match these up. It seemed to do ok on some test IPs that I created. I just created the city_check field to remember where the IP should belong. The City field is being calculated dynamically in the dynamic replace.

[danrh · Accepted solution]

Definitely a fun one.  Here's an option using an iterative macro.  I'm assuming that in a given range, when one of the numbers hits 255, it turns back to 0 and the number to it's left increments 1 (I'm not an IP expert by any means, so correct me if I'm wrong here).

I think I'll hang on to this one!

[Chris_Rodgers]

You are absolutely right on having the IP range go from 0-255. That is a really cool macro!

[danrh]

@Chris_Rodgers found an error in my previous macro.  Here it is again with a minor update!