• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

find partial matches in list of IP subnets

LucyJupiter

LucyJupiter

New Member
Hello all,

I am looking for a way to find partial matches in a long list of IP subnets. We have List A of about 900 IP addresses from users that have visited a product, and List B which is about eleven thousand rows of multiple, space separated IP subnets of users who are allowed access to the product.

We're trying to find out how many of the List A (IP addresses that represent visits) are from allowed users (List B.) The first problem is that one list is IP addresses and one list is IP subnets. I *think* the first two octets of each should be consistent if they are from the same institution (as in IP address 111.222.333.444 and subnet 111.222.666/777 are from the same place since the first two octets 111.222 are the same) but that might just be wrong. (I know this is an Excel forum, but if anyone can comment on that I'd appreciate the knowledge!)

So, we've separated out all of the unique first two octets of List A into their own column. If the first two octet match is a good strategy, is there some way to use an IF function to go through the thousands of space separated IP subnets to find any partial matches in those first two octets? Here's an idea of what our data looks like:

Example List A--first two octets
1.253
102.128
103.11
103.6
104.208
104.213

Example List B (one row represents one institution's allowed IP subnets)
100.100.24.43/32
100.100.24.43/32 147.222.0.0/16 147.222.24.32/32
100.100.24.43/32 147.137.0.0/16 132.174.255.253/32
104.1.77.112/30 104.0.217.160/28 50.242.154.148/30
108.199.166.145/32 108.199.166.146/31 108.166.188.148/31
108.220.98.45/32

MANY thanks for any comments and suggestions, and please let me know if anything is unclear.

-LucyJupiter
 
I'd do some data maniuplation to List B first, and then we can compare pretty easy.

1. We need to get each IP into it's own cell. Select the list, then go to Data - Text to Columns. Select Delimited, Next, Space, Finish. Now they're each in their own cell.
2. Cut/Paste the split data so that it's all in a single column. I'll assume it's in col B, and list A is in col A.
3. To get the subnet of List B, we'll use formula in col C:
=TRIM(LEFT(SUBSTITUTE(B2,".",REPT(" ",999)),999))
Now we only have the subnext.
4. Finally, we can compare. In col D, use formula:
=ISNUMBER(MATCH(C2,A$1:A$900,0))
Anywhere this column displays true, you have a valid IP. :)
 
This was tremendously helpful, THANK YOU! We have the data we need! :D

I ended up using concatenate to put the first two & first three octets into a single cell (we analyzed with matches of both) since I neglected to mention that some IP addresses are like 111.222.333.444 and some are more like 11.22.33.44 or even 11.222.33.444. I used find/replace to insert a space before the "." in the IP address column, then went through step 1, then concatenated them accordingly. The ISNUMBER formula was just what I needed. I really appreciate your response!!
 
You're very welcome, Lucy. Thanks for responding back and letting us know what the final solution was. :)
 
You must log in or register to reply here.
Back
Top