retrieve data from two columns and sort

J

Joe

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?
 
P

Pete_UK

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
 
J

Joe

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
 
J

Joe

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
 
G

Glenn

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)
 
G

Glenn

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,".",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)

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.
 
J

Joe

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,".",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)}

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
 
J

Joe

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.
 
J

Joe

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?
 
G

Glenn

Joe said:
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?
Click to expand...


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
 
G

Glenn

Joe said:
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.
Click to expand...

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(NamesPlusIP,MATCH(address,1stColumn,0),1),
INDEX(NamesPlusIP,MATCH(address,2ndColumn,0),1))

Take a look here:

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

and see if you can piece it together.
 
J

Joe

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

Joe

Glenn said:
Joe said:
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?
Click to expand...


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
Click to expand...
 
J

Joe

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 said:
Ok, I see... Most interesting. Thank you so much for your help.

Joe

Glenn said:
Joe said:
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?
Click to expand...


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
Click to expand...
Click to expand...
 
J

Joe

Works like a champ! You're the Man!

Glenn said:
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)))
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA - retrieve data on last create named range from previous inserted named range 0
Excel Comparing two columns 1
Two columns Data Validation and Password Recovery 1
Comparing data from two columns 2
Retreive data for same agent 3
Compare identifier columns from 2 sheets - remove rows that are dups 2
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Function or Formula help - get data from another sheet 10

Top