I have two colums of IP addresses. One is for wireless cards and the other
for hardwired. Some of the cells in each columns have static IP addresses for
the device referenced on that row. How can I retreive the IP addresses from
both columns into a single column automatically and that will keep adding as
data is added to the original two columns?

Post some examples of your data, showing what you have and what you
want to achieve.

If in column C you want to join the contents of column A with those of
column B, you can use this in C1:

=A1 & B1

and copy down.

Hope this helps.

Pete

On Jan 27, 5:35*am, Joe wrote:
I have two colums of IP addresses. One is for wireless cards and the other
for hardwired. Some of the cells in each columns have static IP addresses for
the device referenced on that row. How can I retreive the IP addresses from
both columns into a single column automatically and that will keep adding as
data is added to the original two columns?

Here is a simple example: I want to be able to read column A and column B and
come up with column C. Note that column A and B can be just about any number
and in any order. In my actual case this will be used for IP numbers like
100.100.100.1, 100.100.100.2, 100.100.100.3, 100.100.100.10. The sort is also
an issue since IP numbers don't use a leading zeros in the last part of the
number the sort would go like this 100.100.100.1, 100.100.100.10,
100.100.100.2, etc.

A B C

1 1
2 5 2
3 3
6 5
6
7 7


"Pete_UK" wrote:

Post some examples of your data, showing what you have and what you
want to achieve.

If in column C you want to join the contents of column A with those of
column B, you can use this in C1:

=A1 & B1

and copy down.

Hope this helps.

Pete

On Jan 27, 5:35 am, Joe wrote:
I have two colums of IP addresses. One is for wireless cards and the other
for hardwired. Some of the cells in each columns have static IP addresses for
the device referenced on that row. How can I retreive the IP addresses from
both columns into a single column automatically and that will keep adding as
data is added to the original two columns?

Is this what you are looking for? Assuming headers in row 1 and data in A3:B7,
put this in C2 and copy down:

=SMALL($A$2:$B$7,ROW()-1)


Joe wrote:
Here is a simple example: I want to be able to read column A and column B and
come up with column C. Note that column A and B can be just about any number
and in any order. In my actual case this will be used for IP numbers like
100.100.100.1, 100.100.100.2, 100.100.100.3, 100.100.100.10. The sort is also
an issue since IP numbers don't use a leading zeros in the last part of the
number the sort would go like this 100.100.100.1, 100.100.100.10,
100.100.100.2, etc.

A B C

1 1
2 5 2
3 3
6 5
6
7 7


"Pete_UK" wrote:

Post some examples of your data, showing what you have and what you
want to achieve.

If in column C you want to join the contents of column A with those of
column B, you can use this in C1:

=A1 & B1

and copy down.

Hope this helps.

Pete

On Jan 27, 5:35 am, Joe wrote:
I have two colums of IP addresses. One is for wireless cards and the other
for hardwired. Some of the cells in each columns have static IP addresses for
the device referenced on that row. How can I retreive the IP addresses from
both columns into a single column automatically and that will keep adding as
data is added to the original two columns?

Here is a better example. I forgot the row names in the first one:

A B C

Fred 1 1
Clem 2 5 2
Harv 3 3
Biff 6 5
Travis 6
Buster 7 7


"Pete_UK" wrote:

Post some examples of your data, showing what you have and what you
want to achieve.

If in column C you want to join the contents of column A with those of
column B, you can use this in C1:

=A1 & B1

and copy down.

Hope this helps.

Pete

On Jan 27, 5:35 am, Joe wrote:
I have two colums of IP addresses. One is for wireless cards and the other
for hardwired. Some of the cells in each columns have static IP addresses for
the device referenced on that row. How can I retreive the IP addresses from
both columns into a single column automatically and that will keep adding as
data is added to the original two columns?

So, if you have names in column A and IP addresses in B and C, you want a sorted
list of all used IP addresses in another column. If that's correct, then this
should work. If not, you need to post a clearer explanation of what you are
trying to accomplish.

Select your range of IP addresses and name it "IP". Then put the following in D2:

=SMALL(IF(IP="","",TRIM(LEFT(SUBSTITUTE(IP,".",REP T(" ",9)),3))*10^9+
TRIM(MID(SUBSTITUTE(IP,".",REPT(" ",9)),11,10))*10^6+
TRIM(MID(SUBSTITUTE(IP,".",REPT(" ",9)),21,10))*10^3+
TRIM(MID(SUBSTITUTE(IP,".",REPT(" ",9)),31,10))),ROW()-1)

This is an array formula, so commit with CTRL+SHIFT+ENTER.

Then put this in E2 to convert back to IP format:

=INT(D2/10^9)&"."&INT(MOD(D2,10^9)/10^6)&"."&INT(MOD(D2,10^6)/10^3)&"."&--RIGHT(D2,3)

Copy both down as needed. Hide column D if desired.

Joe wrote:
Here is a better example. I forgot the row names in the first one:

A B C

Fred 1 1
Clem 2 5 2
Harv 3 3
Biff 6 5
Travis 6
Buster 7 7


"Pete_UK" wrote:

Post some examples of your data, showing what you have and what you
want to achieve.

If in column C you want to join the contents of column A with those of
column B, you can use this in C1:

=A1 & B1

and copy down.

Hope this helps.

Pete

On Jan 27, 5:35 am, Joe wrote:
I have two colums of IP addresses. One is for wireless cards and the other
for hardwired. Some of the cells in each columns have static IP addresses for
the device referenced on that row. How can I retreive the IP addresses from
both columns into a single column automatically and that will keep adding as
data is added to the original two columns?

Glenn,

I created a simple example as shown below. Because of space limitations I am
only showing the last part of the IP address just so it will fit below. In my
actual test case the full IP address is in the cell. For instance in B2 there
is 123.123.123.1 not just 1 by itself. In C3 there is 123.123.123.10 not just
10 by itself, etc.

All I get in columns D and E are #VALUE errors.

A B C D E
1
2 Fred 1 #VALUE! #VALUE!
3 Clem 2 10 #VALUE! #VALUE!
4 Harv 3 #VALUE! #VALUE!
5 Biff 14 6 #VALUE! #VALUE!
6 Travis #VALUE! #VALUE!
7 Buster 7 #VALUE! #VALUE!


I highlighted B2 through C7 and labled it IP as you instructed.

The formula that shows up in D2 is:

{=SMALL(IF(IP="","",TRIM(LEFT(SUBSTITUTE(IP,".",RE PT("
",9)),3))*10^9+TRIM(MID(SUBSTITUTE(IP,".",REPT ("
",9)),11,10))*10^6+TRIM(MID(SUBSTITUTE(IP,".",REPT ("",9)),21,10))*10^3+TRIM(MID(SUBSTITUTE(IP,".",RE PT(" ",9)),31,10))),ROW()-1)}

The forumula that shows up in E2 is:

=INT(D2/10^9)&"."&INT(MOD(D2,10^9)/10^6)&"."&INT(MOD(D2,10^6)/10^3)&"."&--RIGHT(D2,3)

I then dragged them down.

What I would like to come up with is for column E to list the IP's in order
that are pulled from column A and column B As shown below. Again, keep in
mind that the numbers in the columns A through E below are only displayed
with the last part of the IP address just because if I typed in the whole IP
address it would not fit in this space. The numbers to the left of the names
are just the row numbers.

A B C D E
1
2 Fred 1 1
3 Clem 2 10 2
4 Harv 3 3
5 Biff 14 6 6
6 Travis 7
7 Buster 7 10

14


Perhaps if you couild show me how to do this with just simple numbers
instead of IP numbers to start with I could understand the formula better.
Then we can address the sorting of the full IP numbers.

Thanks Glenn.

Joe

"Glenn" wrote:

So, if you have names in column A and IP addresses in B and C, you want a sorted
list of all used IP addresses in another column. If that's correct, then this
should work. If not, you need to post a clearer explanation of what you are
trying to accomplish.

Select your range of IP addresses and name it "IP". Then put the following in D2:

=SMALL(IF(IP="","",TRIM(LEFT(SUBSTITUTE(IP,".",REP T(" ",9)),3))*10^9+
TRIM(MID(SUBSTITUTE(IP,".",REPT(" ",9)),11,10))*10^6+
TRIM(MID(SUBSTITUTE(IP,".",REPT(" ",9)),21,10))*10^3+
TRIM(MID(SUBSTITUTE(IP,".",REPT(" ",9)),31,10))),ROW()-1)

This is an array formula, so commit with CTRL+SHIFT+ENTER.

Then put this in E2 to convert back to IP format:

=INT(D2/10^9)&"."&INT(MOD(D2,10^9)/10^6)&"."&INT(MOD(D2,10^6)/10^3)&"."&--RIGHT(D2,3)

Copy both down as needed. Hide column D if desired.

Joe wrote:
Here is a better example. I forgot the row names in the first one:

A B C

Fred 1 1
Clem 2 5 2
Harv 3 3
Biff 6 5
Travis 6
Buster 7 7


"Pete_UK" wrote:

Post some examples of your data, showing what you have and what you
want to achieve.

If in column C you want to join the contents of column A with those of
column B, you can use this in C1:

=A1 & B1

and copy down.

Hope this helps.

Pete

On Jan 27, 5:35 am, Joe wrote:
I have two colums of IP addresses. One is for wireless cards and the other
for hardwired. Some of the cells in each columns have static IP addresses for
the device referenced on that row. How can I retreive the IP addresses from
both columns into a single column automatically and that will keep adding as
data is added to the original two columns?

Please put a sample of your spreadsheet on www.savefile.com and I will take a
look at it. Just post back the link.


Joe wrote:
Glenn,

I created a simple example as shown below. Because of space limitations I am
only showing the last part of the IP address just so it will fit below. In my
actual test case the full IP address is in the cell. For instance in B2 there
is 123.123.123.1 not just 1 by itself. In C3 there is 123.123.123.10 not just
10 by itself, etc.

All I get in columns D and E are #VALUE errors.

A B C D E
1
2 Fred 1 #VALUE! #VALUE!
3 Clem 2 10 #VALUE! #VALUE!
4 Harv 3 #VALUE! #VALUE!
5 Biff 14 6 #VALUE! #VALUE!
6 Travis #VALUE! #VALUE!
7 Buster 7 #VALUE! #VALUE!


I highlighted B2 through C7 and labled it IP as you instructed.

The formula that shows up in D2 is:

{=SMALL(IF(IP="","",TRIM(LEFT(SUBSTITUTE(IP,".",RE PT("
",9)),3))*10^9+TRIM(MID(SUBSTITUTE(IP,".",REPT ("
",9)),11,10))*10^6+TRIM(MID(SUBSTITUTE(IP,".",REPT ("",9)),21,10))*10^3+TRIM(MID(SUBSTITUTE(IP,".",RE PT(" ",9)),31,10))),ROW()-1)}

The forumula that shows up in E2 is:

=INT(D2/10^9)&"."&INT(MOD(D2,10^9)/10^6)&"."&INT(MOD(D2,10^6)/10^3)&"."&--RIGHT(D2,3)

I then dragged them down.

What I would like to come up with is for column E to list the IP's in order
that are pulled from column A and column B As shown below. Again, keep in
mind that the numbers in the columns A through E below are only displayed
with the last part of the IP address just because if I typed in the whole IP
address it would not fit in this space. The numbers to the left of the names
are just the row numbers.

A B C D E
1
2 Fred 1 1
3 Clem 2 10 2
4 Harv 3 3
5 Biff 14 6 6
6 Travis 7
7 Buster 7 10

14


Perhaps if you couild show me how to do this with just simple numbers
instead of IP numbers to start with I could understand the formula better.
Then we can address the sorting of the full IP numbers.

Thanks Glenn.

Joe

"Glenn" wrote:

So, if you have names in column A and IP addresses in B and C, you want a sorted
list of all used IP addresses in another column. If that's correct, then this
should work. If not, you need to post a clearer explanation of what you are
trying to accomplish.

Select your range of IP addresses and name it "IP". Then put the following in D2:

=SMALL(IF(IP="","",TRIM(LEFT(SUBSTITUTE(IP,".",REP T(" ",9)),3))*10^9+
TRIM(MID(SUBSTITUTE(IP,".",REPT(" ",9)),11,10))*10^6+
TRIM(MID(SUBSTITUTE(IP,".",REPT(" ",9)),21,10))*10^3+
TRIM(MID(SUBSTITUTE(IP,".",REPT(" ",9)),31,10))),ROW()-1)

This is an array formula, so commit with CTRL+SHIFT+ENTER.

Then put this in E2 to convert back to IP format:

=INT(D2/10^9)&"."&INT(MOD(D2,10^9)/10^6)&"."&INT(MOD(D2,10^6)/10^3)&"."&--RIGHT(D2,3)

Copy both down as needed. Hide column D if desired.

Joe wrote:
Here is a better example. I forgot the row names in the first one:

A B C

Fred 1 1
Clem 2 5 2
Harv 3 3
Biff 6 5
Travis 6
Buster 7 7


"Pete_UK" wrote:

Post some examples of your data, showing what you have and what you
want to achieve.

If in column C you want to join the contents of column A with those of
column B, you can use this in C1:

=A1 & B1

and copy down.

Hope this helps.

Pete

On Jan 27, 5:35 am, Joe wrote:
I have two colums of IP addresses. One is for wireless cards and the other
for hardwired. Some of the cells in each columns have static IP addresses for
the device referenced on that row. How can I retreive the IP addresses from
both columns into a single column automatically and that will keep adding as
data is added to the original two columns?