IP range manipulation in Excel?

[DannyTEM]

I am wondering whether the following is possible using a simple function in Excel? I have a column of class C IP ranges like below:

145.45.67
145.45.68
145.45.69
145.45.70
145.45.71
145.45.75
145.45.76
145.45.77
145.46.11
145.46.12
145.46.13

I need them in range format like:

145.45.67:145.45.71
145.45.75:145.45.77
145.46.11:145.46.13

I am happy with general number ranges but because of the IP format I am having trouble matching the concurrent numbers and terminating the range where there is a gap. The list ordering is also not helping as it orders be number so 1, 11 and 111 are all follow each other as opposed to 1, 2, 3 etc.

Been scratching my head for a while on this - any help would be appreciated.

Thx,

DT

Dan Morgan -

http://www.ski-review.com

 

[SkipVought]

Hi,


First, make a helper column(s) for sorting. I'd simply copy the column to the adjacent column and use Data/text to columns - Delimiter . Then sort on those 3 columns.

The its just a matter of concatenation.

Skip,
[sub]
[red][/red]
[/sub]

 

[DannyTEM]

Okay, been thinking about this logically and I figure that instead of C ranges (while this would be great as it would mean much less data/rows) I could duplicate the columns, append .0 to every cell in one and add .255 to every cell in the other, stick a range delimiter like : or - in all cells in a column between the two, then paste the lot into a text editor and trim the white space.

Anyone know how to append data as a suffix to a row?

Cheers,

DT

Dan Morgan -

http://www.ski-review.com

 

[SkipVought]

[tt]
=A1&"what you want to append"
[/tt]


Skip,
[sub]
[red][/red]
[/sub]

 

[DannyTEM]

Hey Skip - thanks for those replies. I was half way through typing my first reply when you first posted so sorry if you thought I was ignoring your response - I'll check them both out.

Dan Morgan -

http://www.ski-review.com

 

[DannyTEM]

Using the &= tip I have managed to achieve what I mentioned in my second post.

With regards to your first post, thats a great tip on Text to Columns (I never knew about it) - the concatenation might take a while as there are a lot of IP's but the time required has been cut into thanks to that post! Cheers!

Dan Morgan -

http://www.ski-review.com

 

[SkipVought]

"...the concatenation might take a while ..."

Should take you all of 3 seconds if your data is CONTIGUOUS.

Enter ONE expression. Then SELECT the cell containing that expression

Notice the little SQUARE in the lower RH corner of the selected cell boundary. DOUBLE-CLICK on this little box.

VOLA!!!!!!

Skip,
[sub]
[red][/red]
[/sub]

 

[DannyTEM]

Actually I am having problems with the steps outlined.

Col A is blank, Col B is the original data, and columns C, D & E is Col B split into 3 columns delimited. Sorting on column E is okay, but however because both the middle and first number (i.e. 145 & 45 from 145.45.67) also need to be concurrent it doesn't work. The E column is nicely 0-255 ascending but the previous 2 numbers are all out of sync.

Did I miss a step?

Dan Morgan -

http://www.ski-review.com

 

[SkipVought]

Rather than DESCRIBING your data, please post an example of the data, representative of the problem you are facing, and explain the problem.

Skip,
[sub]
[red][/red]
[/sub]

 

[DannyTEM]

I have figured it out, but for the purpose of answering the request...:

I'll repaste a sample of the starting data from the original post:

Current:

145.45.67
145.45.68
145.45.69
145.45.70
145.45.71
145.45.75
145.45.76
145.45.77
145.46.11
145.46.12
145.46.13

Goal:

145.45.67:145.45.71
145.45.75:145.45.77
145.46.11:145.46.13

See how the last three rows of the Current data moves from 45 to 46 in the second octet and the third octet is a different range. There are also times when the first octet changes (currently 145).

So it could be 157.89.01:157.89.56

So what I am after is the rows sorted using the final octet but only when the first and second octet are identical.

So as per the open line of this post, I have figred out how to get there. But I am still a little stuck on how to concantenate automatically as the data is not 'contiguous' due to the breaks in the third octet (range 0 through 255).

Cheers,

DT

Dan Morgan -

http://www.ski-review.com

 

[SkipVought]

"...the data is not 'contiguous' due to the breaks in the third octet (range 0 through 255)."

Where is the EXAMPLE of this?

Skip,
[sub]
[red][/red]
[/sub]

 

[DannyTEM]

81 132 253
81 132 254
81 132 255
81 151 8
81 151 9
81 151 10

Consider the above data in 3 columns.

81.151.0 through 81.151.7 does not exist in that data, so concatenation needs to skip those and produce:

81.132.253:81.132.255
81.151.8:81.151.10

All the numbers shown above can only be between 0 and 255.

My eyes are bleeding and my head is pounding so maybe I am missing something obvious, perhaps even the proper definition of contiguous.

Thanks,

DT

Dan Morgan -

http://www.ski-review.com

 

[SkipVought]

What we have here is a failure to communicate!

In Excel, a RANGE refers to CELL ADDRESSES.

You are referring to is data from thru ranges.

What you are trying to do is quite difficult without VBA code, although I am sure that it can be done with sheet functions.

let me think about it and maybe someone else can jump in with a solution.

Skip,
[sub]
[red][/red]
[/sub]

 

[yogia]

Hi DannyTEM:

Let us have a look at the following and see if I have understood you correctly ...

    A     B      C     D    E     F      G 
   ----------------------------------------
1 | 81	132	253		81	132	81.132.253:81.132.255
2 | 81	132	254		81	151	81.151.8:81.151.10
3 | 81	132	255				
4 | 81	151	8				
5 | 81	151	9				
6 | 81	151	10

formula in cell G1 is ...

=E1&"."&F1&"."&INDEX(C:C,MATCH(E1&F1,INDEX($A$1:$A$6&$B$1:$B$6,0),0))&":"&E1&"."&F1&"."&INDEX(C:C,MATCH(E1&F1,INDEX($A$1:$A$6&$B$1:$B$6,0)))

this formula is then copied down.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi Danny:

I ran my formulas through for your data in the first post of this thread and my results partially agree with you.

    A      B     C    D    E      F     G 
   ----------------------------------------
1 | 145	45	67		145	45	145.45.67:145.45.77
2 | 145	45	68		145	46	145.46.11:145.46.13
3 | 145	45	69				
4 | 145	45	70				
5 | 145	45	71				
6 | 145	45	75				
7 | 145	45	76				
8 | 145	45	77				
9 | 145	46	11				
10| 145	46	12				
11| 145	46	13

My formula in cell G1 is ...

=E1&"."&F1&"."&INDEX(C:C,MATCH(E1&F1,INDEX($A$1:$A$11&$B$1:$B$11,0),0))&":"&E1&"."&F1&"."&INDEX(C:C,MATCH(E1&F1,INDEX($A$1:$A$11&$B$1:$B$11,0)))
this formula is then copied down.

You show three resulting records whereas my formulas produce only two records ... so what gives. Please explain the logic of how three records are extracted from the data you have posted and then let us take it from there.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[DannyTEM]

Hi Yogi,

Thanks for stopping buy.

The reason those results differ is because the first range, should start at 145.45.67 and should stop at 145.45.71 because the following does not exist:

145.45.72
145.45.73
145.45.74

The results of your data include these in the data.

Can I ask how you got your data into columns E and F? Manually?

Thanks,

DT



Dan Morgan -

http://www.ski-review.com

 

[yogia]

Can I ask how you got your data into columns E and F? Manually?

A       B       C       D   E     F      G
-----------------------------------------------------------
1 | Field1Field2Field3			Field2	
2 | 81	132	253		81	132	81.132.Field3:81.132.254
3 | 81	132	254		81	151	81.151.255:81.151.9
4 | 81	132	255				
5 | 81	151	8				
6 | 81	151	9				
7 | 81	151	10				
8 |

1) I started with the 3 columns (a, B, and C) of parsed data as you had presented in one of the posts in this thread. This is also easily parsed from nnn.nnn.nnn using '.' as the delimiter.

2) for colum F, I can get the values in two ways ...
o by using AdvancedFilter and extracting Unique_records from column B of the parsed data as refered to in 1) above
or
o by using UniqueValues function from the MoreFunc Add-in ... so for the values in cell F2 and F3, I used the following array formula ...
=UNIQUEVALUES(B2:B7,1)

3) then I can extract the values in column E from a simple LOOKUP function ... in cell E2, I used the formula:

=LOOKUP(F2,B:B,A:A)
and I copied it down for cell E3.

I hope this helps.



Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com