I have a list of IP addresses, over 30K records. I need to filter out those
records which meet specific IP range i.e. within:
3.xxx.xxx.7
3.xxx.xxx.71

I have seven of these ranges. How can I do this programatically. I can't
think of a way to do this in a pivot table and will like to stay away from VB
if possible because I don't know it. But I'll take anything at this point. I
thought of doing custom filter with 'contains' 3.*.7 but it is giving me
records that end in something other than .7 AND I can only do up to 2
criteria not seven.

I have a short timeframe for this. I use Excel 2003 on winXP Pro 2002 SP3

Thank you for your time!!!!

--
Hile

Create a helper column B.

In B2: =AND(LEFT(A2)="3",MID(A2,11,1)="7")
Auto Filter the TRUE


"Hile" wrote:

I have a list of IP addresses, over 30K records. I need to filter out those
records which meet specific IP range i.e. within:
3.xxx.xxx.7
3.xxx.xxx.71

I have seven of these ranges. How can I do this programatically. I can't
think of a way to do this in a pivot table and will like to stay away from VB
if possible because I don't know it. But I'll take anything at this point. I
thought of doing custom filter with 'contains' 3.*.7 but it is giving me
records that end in something other than .7 AND I can only do up to 2
criteria not seven.

I have a short timeframe for this. I use Excel 2003 on winXP Pro 2002 SP3

Thank you for your time!!!!

--
Hile

Thank you but that does not cover all my scenarios. Though the first digit is
3 in all it is not 7 in the rest. It would have to be nested somehow to
include all. Here's the list of all 7:

3.xxx.xxx.7
3.xxx.xxx.71
3.xxx.xxx.135
3.xxx.xxx.199
3.xxx.xxx.110
3.xxx.xxx.111
3.xxx.xxx.115

Thanks for coming to my aide. :-)
--
Hile


"Teethless mama" wrote:

Create a helper column B.

In B2: =AND(LEFT(A2)="3",MID(A2,11,1)="7")
Auto Filter the TRUE


"Hile" wrote:

I have a list of IP addresses, over 30K records. I need to filter out those
records which meet specific IP range i.e. within:
3.xxx.xxx.7
3.xxx.xxx.71

I have seven of these ranges. How can I do this programatically. I can't
think of a way to do this in a pivot table and will like to stay away from VB
if possible because I don't know it. But I'll take anything at this point. I
thought of doing custom filter with 'contains' 3.*.7 but it is giving me
records that end in something other than .7 AND I can only do up to 2
criteria not seven.

I have a short timeframe for this. I use Excel 2003 on winXP Pro 2002 SP3

Thank you for your time!!!!

--
Hile

I just tried this formula for each of the ranges in 7 diff columns and
realized, the Mid function will not always work. Some of the digits in the
xxx.xxx configuration can be either 3 or 2 digits long.

i.e. I got a TRUE for =AND(LEFT($B3072)="3",MID($B3072,11,2)="71"), but the
IP it matched was 3.xxx.xx.171 - so a false positive. Also though it works
for 3.xxx.xxx.7 it also returns TRUE for IPs which have digits after the
seven like 3.xxx.xxx.70 or 711.

It has made it easier to review the list though breaking it into more
manageable sections. So if we can't resolve the issue, I at least have an
easier way to get there than item by item. Thank you.

--
Hile


"Teethless mama" wrote:

Create a helper column B.

In B2: =AND(LEFT(A2)="3",MID(A2,11,1)="7")
Auto Filter the TRUE


"Hile" wrote:

I have a list of IP addresses, over 30K records. I need to filter out those
records which meet specific IP range i.e. within:
3.xxx.xxx.7
3.xxx.xxx.71

I have seven of these ranges. How can I do this programatically. I can't
think of a way to do this in a pivot table and will like to stay away from VB
if possible because I don't know it. But I'll take anything at this point. I
thought of doing custom filter with 'contains' 3.*.7 but it is giving me
records that end in something other than .7 AND I can only do up to 2
criteria not seven.

I have a short timeframe for this. I use Excel 2003 on winXP Pro 2002 SP3

Thank you for your time!!!!

--
Hile