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