Excel IP Address Parse and Search

[birdman91]

Hello All,

I've been lurking through these forums in search of a result for my problem and could not find a complete solution. So I made an account to see if someone out there can help me out.

I have a word document that I get regularly that contains hundreds of IP address along with text in this general Format:

We are looking at these IP address 122.122.121.123 and 12.13.51.123 with special attention to the following:
A. 187.213.123.1
B. 232.123.54.012
C. etc
D. etc...

1. 124.123.123.123
2. 18.12.51.32
3. etc....

Right now I am copying and pasting the word document into excel and running the following

Sub removechar()
Dim rng as range
Dim workrng as range
on error resume next
xtitleid = "IPaddress"
set workrng = application.selection
set workrng = application.inputbox("range", xtitleid, working.address, type:=8)
For Each rng in workrng
     xout = ""
     for i = 1 to len(rng.value)
            xtemp = mid(rng.value, i, 1)
            if xtemp like "[0-9.]" then
                xstr = xtemp
            else
                xstr = ""
            end if 
            xout = xout & xstr
      next i 
      rng.value = xout
next
end sub

As you can see it is simply removing everything that is not a number or a period. So as you can imagine I get the following outputs.

122.122.121.12312.13.51.123
.187.213.123.1
.232.123.54.012
.
....

1.124.123.123.123
2.18.12.51.32
3.....

With this I can go through and manually clean up the rows but when I get thousands of rows sometimes it can get tedious. The obvious output I'd like to see is... which deletes empty rows as well.
122.122.121.12
312.13.51.123
187.213.123.1
232.123.54.012
124.123.123.123
18.12.51.32

The next step is where it gets even trickier. I want to be able to cross check those IPs versus a master list to check for duplicates. Now I know I can use conditional formatting to easily highlight duplicates but I also need to check against IP ranges in this format.

[TABLE="width: 500"]
<tbody>[TR]
[TD]2.0.0.0[/TD]
[TD]2.255.255.255[/TD]
[/TR]
[TR]
[TD]15.42.32.0[/TD]
[TD]15.42.40.255[/TD]
[/TR]
[TR]
[TD]145.132.123.0[/TD]
[TD]145.140.255.255[/TD]
[/TR]
</tbody>[/TABLE]


My ideal output and final check workbook, if even possible, would look something like this with the Check IP denoting if it is a duplicate value by highlighting itself or other visual queue.[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Check IPs[/TD]
[TD]Master Single IP list[/TD]
[TD]Master IP Range List[/TD]
[TD]Master IP Range list[/TD]
[/TR]
[TR]
[TD]122.122.121.12[/TD]
[TD]7.0.0.0[/TD]
[TD]2.0.0.0[/TD]
[TD]2.255.255.255[/TD]
[/TR]
[TR]
[TD]312.13.51.123[/TD]
[TD]7.0.0.1[/TD]
[TD]15.42.32.0[/TD]
[TD]15.42.40.255[/TD]
[/TR]
[TR]
[TD]187.213.123.1[/TD]
[TD]7.0.0.2[/TD]
[TD]145.132.123.0[/TD]
[TD]145.140.255.255[/TD]
[/TR]
[TR]
[TD]232.123.54.012[/TD]
[TD]7.0.0.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]124.123.123.123[/TD]
[TD]7.0.0.4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18.12.51.32[/TD]
[TD]7.0.0.5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Where the values in the Check IP Column can be changed fluidly but is checked against the "Master Single IP list" and against all IPs within the ranges of "Master IP Range List"

Any attempts would need to be able to scale to be able to handle thousands of IPs if possible...

I know this is asking a lot, especially for a brand new member, but this would be a great way to streamline a tedious task I do daily...

Thank You in Advance.

 

[oldbrewer]

We are looking at these IP address 122.122.121.123 and 12.13.51.123 with special attention to the following:
A. 187.213.123.1
B. 232.123.54.012
C. etc
D. etc...

1. 124.123.123.123
2. 18.12.51.32
3. etc....

this is not at all clear

show us a bit of the word document and define clearly how you want to manipulate it

 

[jasonb75]

Welcome to the board!

This code should filter out the IP's from the list correctly, I've assumed that your word document occupies 1 column when you copy it to excel, the code will extract the results 1 column to the right rather than overwriting the original data.

Once we establish that this is working correctly then we can start looking at comparing and organising the data.

Option Explicit
Sub removechar()
Application.ScreenUpdating = False
Dim rng As Range, workrng As Range, outrng As Range
Dim outrow As Long, xarr As Long, tmpArr As Variant
Set workrng = Application.InputBox("range", "IP Address", Type:=8)
Set outrng = workrng(1).Offset(0, 1)
For Each rng In workrng
    tmpArr = Split(rng, " ")
    For xarr = 0 To UBound(tmpArr)
        If tmpArr(xarr) Like "#*.#*.#*.#*" Then
            outrng.Offset(outrow) = tmpArr(xarr)
            outrow = outrow + 1
        End If
    Next
Next
Application.ScreenUpdating = True
End Sub

 

[birdman91]

Jason that work perfectly! Pulled out every IP and cross checked with a manual pull I did. Hopefully you have just as good of a solution for the rest of the information.

 

[jasonb75]

Hopefully you have just as good of a solution for the rest of the information.

I haven't thought that far ahead yet lol.

I think that the next part might be easier with conditional formatting, but this would require the master list to be sorted in ascending order, something on the basis of
=OR(Check IP<=VLOOKUP(Check IP,Master IP Range List,2,1),MATCH(Check IP, Master Single IP List,0)) should do the trick.

For the final part, if you have a sheet ready made with the master lists in place, then you could simply change the destination of outrng in the code to submit the extracted IP's directly into the first column of the master sheet.

 

[birdman91]

Hey there got caught up in other stuff last night but I'm back trying to complete this!

So I'm a bit confused so I made a table to see if you would be able to break it down barney style for me...

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]IP List[/TD]
[TD]Single IP List[/TD]
[TD]IP Range 1[/TD]
[TD]IP Range 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.4.1.49[/TD]
[TD]4.21.123.51[/TD]
[TD]2.0.0.0[/TD]
[TD]2.255.255.255[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]213.31.21.15[/TD]
[TD]56.13.52.21[/TD]
[TD]3.4.12.0[/TD]
[TD]3.4.14.255[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2.1.1.1[/TD]
[TD]12.53.1.4[/TD]
[TD]1.0.1.0[/TD]
[TD]1.5.0.255[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3.4.12.12[/TD]
[TD]76.43.123.21[/TD]
[TD]213.31.48.0[/TD]
[TD]213.31.48.255[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4.21.123.51[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In some way the hope would be for the List in Column A to be highlighted or some other feature to show if it is a duplicate of the single IP list or within the range of columns C and D.

So if it worked Cell A2, A4, A5, and A6 should be highlighted or shown to be a duplicated while A3 is left alone.

I tried your conditional formatting and kept getting a #Name? error.

Again thank you for all your help... hopefully you're still around.

 

[jasonb75]

Sorry, I should have been a bit clearer in my post.

The conditional format formula that I suggested had descriptions in it as I didn't know your real sheet ranges,

=OR(Check IP<=VLOOKUP(Check IP,Master IP Range List,2,1),MATCH(Check IP, Master Single IP List,0))

Working with your sample table, that would translate to a formula like

=OR($A2<=VLOOKUP($A2,$C$2:$D$5,2,1),MATCH($A2,$B$2:$B$5,0))

This will only work if the data in columns C and D is sorted by column C ascending.

The formula would work with normal data, but I'm not sure if the formatting of IP addresses will be a problem, it's getting late in my part of the world, I'll have another look tomorrow and see what I can come up with.

 

[birdman91]

Yeah the conditional formatting works well with the one to one matches but it is not able to search the range of the IP because I don't think it knows how to define it. That's why I was thinking vbs could solve it.

I was thinking about trying to split all of the IPs by octet with 000.000.000.000 format into different cells and doing a longer conditional formatting check.

I'll update if I have any epiphanies...

 

[birdman91]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]e
[/TD]
[TD]f
[/TD]
[TD]g
[/TD]
[TD]h
[/TD]
[TD]i
[/TD]
[TD]j
[/TD]
[TD]k
[/TD]
[TD]l
[/TD]
[TD]m
[/TD]
[TD]n
[/TD]
[TD]o
[/TD]
[TD]p
[/TD]
[TD]q
[/TD]
[TD]r
[/TD]
[TD]s
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]002
[/TD]
[TD]001
[/TD]
[TD]001
[/TD]
[TD]001
[/TD]
[TD][/TD]
[TD]012
[/TD]
[TD]053
[/TD]
[TD]001
[/TD]
[TD]004
[/TD]
[TD][/TD]
[TD]001
[/TD]
[TD]000
[/TD]
[TD]001
[/TD]
[TD]000
[/TD]
[TD][/TD]
[TD]001
[/TD]
[TD]005
[/TD]
[TD]000
[/TD]
[TD]255
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]001
[/TD]
[TD]004
[/TD]
[TD]001
[/TD]
[TD]049
[/TD]
[TD][/TD]
[TD]004
[/TD]
[TD]021
[/TD]
[TD]123
[/TD]
[TD]051
[/TD]
[TD][/TD]
[TD]002
[/TD]
[TD]000
[/TD]
[TD]000
[/TD]
[TD]000
[/TD]
[TD][/TD]
[TD]002
[/TD]
[TD]255
[/TD]
[TD]255
[/TD]
[TD]255
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]003
[/TD]
[TD]004
[/TD]
[TD]012
[/TD]
[TD]012
[/TD]
[TD][/TD]
[TD]076
[/TD]
[TD]043
[/TD]
[TD]123
[/TD]
[TD]021
[/TD]
[TD][/TD]
[TD]213
[/TD]
[TD]031
[/TD]
[TD]048
[/TD]
[TD]000
[/TD]
[TD][/TD]
[TD]213
[/TD]
[TD]031
[/TD]
[TD]048
[/TD]
[TD]255
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]213
[/TD]
[TD]031
[/TD]
[TD]021
[/TD]
[TD]015
[/TD]
[TD][/TD]
[TD]056
[/TD]
[TD]013
[/TD]
[TD]052
[/TD]
[TD]021
[/TD]
[TD][/TD]
[TD]003
[/TD]
[TD]004
[/TD]
[TD]012
[/TD]
[TD]000
[/TD]
[TD][/TD]
[TD]003
[/TD]
[TD]004
[/TD]
[TD]014
[/TD]
[TD]255
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]004
[/TD]
[TD]021
[/TD]
[TD]123
[/TD]
[TD]051
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So I've been playing around with this trying to figure out a solution. I thought I had a solution for the simple comparing the IPs in columns A-D with the IPs in columns F-I but I've gotten some false negatives. maybe there is a cleaner solution than this...

=and(if(vlookup(a1,f:f,1,0),true,false),if(vlookup(b1,g:g,1,0),true,false),if(vlookup(c1,h:h,1,0),true,false),if(vlookup(d1,i:i,1,0),true,false),true,false))

And just looking at the data sets I can see why I'm getting false negatives. When ever a cell has 000 it will always come back false. Which of course is bad. Not sure how to fix that but I'll play around with it.

The second part where I'm comparing the IP List to the Ranges on the side is definitely stumping. I don't think it's possible with conditional formatting. I've tried inbetween or = commands to no avail. I think the problem comes that when searching the array it doesn't know to search within the range say 2.0.0.0-2.255.255.255 then search through the next range. Instead I think it is searching through all on the left then all on the right making everything come back false or with errors.

this is getting pretty complex...

 

[jasonb75]

Re-evaluating the task, I think suggesting conditional formatting was a bad idea, I'm adapting the code from post #3 to check each IP address from the word document against the other lists as part of the same process.

Bear with me, it could take a while to get this working correctly.