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?

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?

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?

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?

Here you go: http://www.savefile.com/files/1985124


"Glenn" wrote:

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?

Joe wrote:
Here you go: http://www.savefile.com/files/1985124




You are missing a space between two quotes ("" instead of " ") in one of the
REPT() functions in column D.

That works great. Thank you!

I actually have the main data on a different sheet then the sheet that is
listing the used static IP's. I deleted the IP name on the sheet I was on and
named the main two columns from the main data sheet to IP and it pulls the
Static IP list just fine. Is there a way to also pull from the other columns.
I'm thinking I could do a VLOOKUP on the IP and return the adjacent
description.

Thanks again.

"Glenn" wrote:

Joe wrote:
Here you go: http://www.savefile.com/files/1985124




You are missing a space between two quotes ("" instead of " ") in one of the
REPT() functions in column D.

Glenn,

I am having a hard time figuring out how the formula you wrote works. Can
you dumb it down for me with plain numbers instead of IP numbers so I can
understand it?

"Joe" wrote:

Here you go: http://www.savefile.com/files/1985124


"Glenn" wrote:

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?

Joe wrote:
Glenn,

I am having a hard time figuring out how the formula you wrote works. Can
you dumb it down for me with plain numbers instead of IP numbers so I can
understand it?



The original formula:

=SMALL(
IF(IP="","",
TRIM(LEFT(SUBSTITUTE(IP,".",REPT(" ",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)

if used with "plain numbers" would be the non-array formula:

=SMALL(IP,ROW()-1)

The "ROW()-1" portion, when copied down from row 2 would result in the numbers
1, 2, 3, etc.

The SMALL() function would then retrieve the corresponding value from the array
"IP".


The more difficult part of this was to convert the IP addresses to numbers
(because SMALL() only works with numbers) that would sort properly and could
easily be converted back to IP addresses.

Each of the TRIM(LEFT(SUBSTITUTE(REPT() or TRIM(MID(SUBSTITUTE(REPT() formulas
isolate and convert one byte of the IP address into a portion of a 12 digit
number (three digits for each byte).

The SUBSTITUTE(REPT() combination replaces the "." separator in the IP address
with 9 spaces.

123.12.0.101
becomes
123 12 0 101

The LEFT() or MID() functions grab the appropriate section of the resulting
string and the TRIM() function removes the leading and trailing spaces from that
section. [I could have used RIGHT() for the last one] Then they are multiplied
by the appropriate power of 10 to position them in the final 12 digit number.

123 12 0 101
becomes
123,012,000,101

Now SMALL() has an array of numbers to sort through.

This would be a little easier if Excel had a function that would easily allow
you to find the position of the nth occurrence of "." in the IP address.

The other formula:

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

breaks down the 12 digit number into it's original IP address format. The INT()
and MOD() formulas are pretty normal and are covered in the help file.

123,012,000,101
goes back to
123.12.0.101

Joe wrote:
That works great. Thank you!

I actually have the main data on a different sheet then the sheet that is
listing the used static IP's. I deleted the IP name on the sheet I was on and
named the main two columns from the main data sheet to IP and it pulls the
Static IP list just fine. Is there a way to also pull from the other columns.
I'm thinking I could do a VLOOKUP on the IP and return the adjacent
description.

Thanks again.


Because the description is to the left of the IP address, you would need to use
an INDEX(MATCH( combination. Because the IP addresses are in two columns, it
would become more complicated. I'm guessing something like this:

=IF(ISNUMBER(MATCH(address,1stColumn,0)),INDEX(Nam esPlusIP,MATCH(address,1stColumn,0),1),
INDEX(NamesPlusIP,MATCH(address,2ndColumn,0),1))

Take a look he

http://www.contextures.com/xlFunctions03.html

and see if you can piece it together.

Ok, I see... Most interesting. Thank you so much for your help.

Joe

"Glenn" wrote:

Joe wrote:
Glenn,

I am having a hard time figuring out how the formula you wrote works. Can
you dumb it down for me with plain numbers instead of IP numbers so I can
understand it?



The original formula:

=SMALL(
IF(IP="","",
TRIM(LEFT(SUBSTITUTE(IP,".",REPT(" ",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)

if used with "plain numbers" would be the non-array formula:

=SMALL(IP,ROW()-1)

The "ROW()-1" portion, when copied down from row 2 would result in the numbers
1, 2, 3, etc.

The SMALL() function would then retrieve the corresponding value from the array
"IP".


The more difficult part of this was to convert the IP addresses to numbers
(because SMALL() only works with numbers) that would sort properly and could
easily be converted back to IP addresses.

Each of the TRIM(LEFT(SUBSTITUTE(REPT() or TRIM(MID(SUBSTITUTE(REPT() formulas
isolate and convert one byte of the IP address into a portion of a 12 digit
number (three digits for each byte).

The SUBSTITUTE(REPT() combination replaces the "." separator in the IP address
with 9 spaces.

123.12.0.101
becomes
123 12 0 101

The LEFT() or MID() functions grab the appropriate section of the resulting
string and the TRIM() function removes the leading and trailing spaces from that
section. [I could have used RIGHT() for the last one] Then they are multiplied
by the appropriate power of 10 to position them in the final 12 digit number.

123 12 0 101
becomes
123,012,000,101

Now SMALL() has an array of numbers to sort through.

This would be a little easier if Excel had a function that would easily allow
you to find the position of the nth occurrence of "." in the IP address.

The other formula:

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

breaks down the 12 digit number into it's original IP address format. The INT()
and MOD() formulas are pretty normal and are covered in the help file.

123,012,000,101
goes back to
123.12.0.101

Well I got it to kinda work. If you get a chance would you see what is wrong
with it?

http://www.savefile.com/files/1987705

Thank you.

"Joe" wrote:

Ok, I see... Most interesting. Thank you so much for your help.

Joe

"Glenn" wrote:

Joe wrote:
Glenn,

I am having a hard time figuring out how the formula you wrote works. Can
you dumb it down for me with plain numbers instead of IP numbers so I can
understand it?



The original formula:

=SMALL(
IF(IP="","",
TRIM(LEFT(SUBSTITUTE(IP,".",REPT(" ",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)

if used with "plain numbers" would be the non-array formula:

=SMALL(IP,ROW()-1)

The "ROW()-1" portion, when copied down from row 2 would result in the numbers
1, 2, 3, etc.

The SMALL() function would then retrieve the corresponding value from the array
"IP".


The more difficult part of this was to convert the IP addresses to numbers
(because SMALL() only works with numbers) that would sort properly and could
easily be converted back to IP addresses.

Each of the TRIM(LEFT(SUBSTITUTE(REPT() or TRIM(MID(SUBSTITUTE(REPT() formulas
isolate and convert one byte of the IP address into a portion of a 12 digit
number (three digits for each byte).

The SUBSTITUTE(REPT() combination replaces the "." separator in the IP address
with 9 spaces.

123.12.0.101
becomes
123 12 0 101

The LEFT() or MID() functions grab the appropriate section of the resulting
string and the TRIM() function removes the leading and trailing spaces from that
section. [I could have used RIGHT() for the last one] Then they are multiplied
by the appropriate power of 10 to position them in the final 12 digit number.

123 12 0 101
becomes
123,012,000,101

Now SMALL() has an array of numbers to sort through.

This would be a little easier if Excel had a function that would easily allow
you to find the position of the nth occurrence of "." in the IP address.

The other formula:

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

breaks down the 12 digit number into it's original IP address format. The INT()
and MOD() formulas are pretty normal and are covered in the help file.

123,012,000,101
goes back to
123.12.0.101

Joe wrote:
Well I got it to kinda work. If you get a chance would you see what is wrong
with it?

http://www.savefile.com/files/1987705

Thank you.



Try this:

=IF(ISNUMBER(MATCH(B5,$G$5:$G$14,0)),INDEX($F$5:$F $14,MATCH(B5,$G$5:$G$14,0)),INDEX($F$5:$F$14,MATCH (B5,$H$5:$H$14,0)))

Works like a champ! You're the Man!

"Glenn" wrote:

Joe wrote:
Well I got it to kinda work. If you get a chance would you see what is wrong
with it?

http://www.savefile.com/files/1987705

Thank you.



Try this:

=IF(ISNUMBER(MATCH(B5,$G$5:$G$14,0)),INDEX($F$5:$F $14,MATCH(B5,$G$5:$G$14,0)),INDEX($F$5:$F$14,MATCH (B5,$H$5:$H$14,0)))