Looking for guidance on comparisons

[scotchtape]

Hi,

I am not sure if I should call this a comparison, but I am not sure where to start. Here is what I am trying to do.

Lets say I have 1 column, column A.

In column A there are many rows of IP ranges:

10.10.1.0-10.10.127.255
10.10.128.0-10.10.255.255

If a A2 row is within a certain range, I would like to place text in B2 stating that the row is in "whatever"

I would have many rows. probably 60, and each row contains a unique IP range. I just need to assign them text in column b if they match a certain defined range. Below is just generic information.

A B
10.10.1.0-10.10.127.255 Apples 1
10.10.128.0-10.10.255.255 Apples 2
10.11.0.0-10.11.127.255 Apples 3
10.11.128.0-10.11.255.255 Orange


Where would I start?

 

[Drrellik]

In column A there are many rows of IP ranges:

10.10.1.0-10.10.127.255
10.10.128.0-10.10.255.255

If you have all of this in A1,A2,A3... ect I think you are going to have a very hard time trying to determine if 10.10.1.1 in your reference cell will fall between your example 10.10.1.0 - 10.10.127.255 with just a formula, I feel quite sure this will need VBA unless you can structure you ranges differently and even then I am not sure how Excel will treat this 'Number' because it is an IP address.

 

[thisoldman]

Are you familiar with Text to Columns?

I think a solution might be devised using Text to Columns and some formulas to convert the IP addresses to decimal integers. The conversion can be done without Text to Columns but the formulas would be long and have a lot of nesting.

An IP4 address is four fields separated by periods, ".". A valid field value must be between zero and 255 (&H00 to &HFF in VBA). We can convert an IP address to an integer by multiplying each field by a power of 255.

Say the IP address is "10.22.31.127". We can convert it to an integer with this equation:
=10 * 255^3 + 22 * 255^2 + 31 * 255^1 + 127 * 255^0

As an Excel formula and removing some unneeded exponents, the equation can be written as:
=Field1 * 255^3 + Field2 * 255^2 + Field3 *255 + Field4

If you convert all IP addresses to decimal integers, you can use greater than and less than comparisons to see if the ranges overlap or use a lookup table to populate the "whatevers".

 

[Drrellik]

	A	B	C	D	E	F
10.10.1.0-10.10.127.255	10.10.1.0	10.10.127.255	10.10.1.1
10.10.1.0	10.10.127.255	10.10.1.0

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: right"]10
[/TD]
[TD="align: right"]23
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1
[/TH]
[TD="align: left"]=FIND("-",A1)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1
[/TH]
[TD="align: left"]=LEN(A1)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1
[/TH]
[TD="align: left"]=LEFT(A1,B1-1)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1
[/TH]
[TD="align: left"]=RIGHT(A1,C1-B1)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2
[/TH]
[TD="align: left"]=LEFT(A1,FIND("-",A1)-1)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2
[/TH]
[TD="align: left"]=RIGHT(A1,LEN(A1)-FIND("-",A1))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


I showed you how I started with each simple formula to achieve my desired results and then how you can simple replace the cell reference with their formulas to build a larger formula and understand the order of operations. I used Conditional formatting on the last cell with a > reference and Excel was able to determine that 10.10.1.1 was larger than 10.10.1.0 hope this helps you, I will play around with the actual vlookup for the cell ranges at work tomorrow but you will probably have to sort the range after the formulas for Vlookup