Prev: Predefined text list
Next: hide columns before printing
From: Deen on 15 Apr 2010 01:52
Hi Every One,

I hope you all are doing well, I required your help to resolve my issue in
excel.

On single worksheet i have two different sheet name as 1.IP Range 2. Result.

Question:
EG:IP Range

Start IP End IP
Area

192.168.1.4 195.182.254.254 AG
10.15.33.10 10.18.56.254
EMEA
10.128.33.5 10.132.40.60 AP

Here it will Continued as well.

On Result sheet i required result like:

IP Area

192.170.30.30 AG
194.168.10.20 AG
10.131.37.20 EMEA
10.170.255.255 NA
10.129.36.8 AP

I required result on area column. Here we have more than 50000 IP's but very
difficult to find the area.

Could any one please help me on this issue. How i can resolve the issue


Thanks in advance
Deen
From: Pete_UK on 15 Apr 2010 08:17
You will have to convert those IP addresses to actual numbers (each
dot represents a power of 256) and then sort the data by start address
to enable you to determine the ranges correctly. Then you would be
able to find the nearest (lower or equal) start address using VLOOKUP,
and then check the end address to see that it is not lower than your
sought address.

Hope this helps.

Pete


On Apr 15, 6:52 am, Deen <D...(a)discussions.microsoft.com> wrote:
> Hi Every One,
>
> I hope you all are doing well, I required your help to resolve my issue in
> excel.
>
> On single worksheet i have two different sheet name as 1.IP Range 2. Result.
>
> Question:
> EG:IP Range
>
> Start IP End IP
> Area
>
> 192.168.1.4 195.182.254.254 AG
> 10.15.33.10 10.18.56.254
> EMEA
> 10.128.33.5 10.132.40.60 AP
>
> Here it will Continued as well.
>
> On Result sheet i required result like:
>
> IP Area
>
> 192.170.30.30 AG
> 194.168.10.20 AG
> 10.131.37.20 EMEA
> 10.170.255.255 NA
> 10.129.36.8 AP
>
> I required result on area column. Here we have more than 50000 IP's but very
> difficult to find the area.
>
> Could any one please help me on this issue. How i can resolve the issue
>
> Thanks in advance
> Deen

From: Deen on 16 Apr 2010 05:35
Hello Pete,

I have done the same as you said, But no luck.... Could you please explain

Regards,
Deen



"Pete_UK" wrote:

> You will have to convert those IP addresses to actual numbers (each
> dot represents a power of 256) and then sort the data by start address
> to enable you to determine the ranges correctly. Then you would be
> able to find the nearest (lower or equal) start address using VLOOKUP,
> and then check the end address to see that it is not lower than your
> sought address.
>
> Hope this helps.
>
> Pete
>
>
> On Apr 15, 6:52 am, Deen <D...(a)discussions.microsoft.com> wrote:
> > Hi Every One,
> >
> > I hope you all are doing well, I required your help to resolve my issue in
> > excel.
> >
> > On single worksheet i have two different sheet name as 1.IP Range 2. Result.
> >
> > Question:
> > EG:IP Range
> >
> > Start IP End IP
> > Area
> >
> > 192.168.1.4 195.182.254.254 AG
> > 10.15.33.10 10.18.56.254
> > EMEA
> > 10.128.33.5 10.132.40.60 AP
> >
> > Here it will Continued as well.
> >
> > On Result sheet i required result like:
> >
> > IP Area
> >
> > 192.170.30.30 AG
> > 194.168.10.20 AG
> > 10.131.37.20 EMEA
> > 10.170.255.255 NA
> > 10.129.36.8 AP
> >
> > I required result on area column. Here we have more than 50000 IP's but very
> > difficult to find the area.
> >
> > Could any one please help me on this issue. How i can resolve the issue
> >
> > Thanks in advance
> > Deen
>
> .
>
 | 
Pages: 1
Prev: Predefined text list
Next: hide columns before printing