|
Vlookup Question
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 |
Vlookup Question
Its pretty easy to do this using a macro..If you are looking for a formula
try the below (bit lengthy) In Result sheet cell A1 enter the IP number In cell B1 enter the below formula. Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" The result for the sample you pasted would be as below (which is different from what you have posted) 192.170.30.30 AG 194.168.10.20 AG 10.131.37.20 AP 10.170.255.255 #N/A 10.129.36.8 AP =INDEX('IP Range'!$C$1:$C$10, MATCH(1,(TEXT(LEFT(SUBSTITUTE('IP Range'!$A$1:$A$10,"."," "),3),"000")& TEXT(MID(SUBSTITUTE('IP Range'!$A$1:$A$10,"."," "),7,6),"000")& TEXT(MID(SUBSTITUTE('IP Range'!$A$1:$A$10,"."," "),13,9),"000")& TEXT(RIGHT(SUBSTITUTE('IP Range'!$A$1:$A$10,"."," "),3),"000")<= TEXT(LEFT(SUBSTITUTE(A1,"."," "),3),"000")& TEXT(MID(SUBSTITUTE(A1,"."," "),7,6),"000")& TEXT(MID(SUBSTITUTE(A1,"."," "),13,9),"000")& TEXT(RIGHT(SUBSTITUTE(A1,"."," "),3),"000"))*( TEXT(LEFT(SUBSTITUTE('IP Range'!$B$1:$B$10,"."," "),3),"000")& TEXT(MID(SUBSTITUTE('IP Range'!$B$1:$B$10,"."," "),7,6),"000")& TEXT(MID(SUBSTITUTE('IP Range'!$B$1:$B$10,"."," "),13,9),"000")& TEXT(RIGHT(SUBSTITUTE('IP Range'!$B$1:$B$10,"."," "),3),"000")= TEXT(LEFT(SUBSTITUTE(A1,"."," "),3),"000")& TEXT(MID(SUBSTITUTE(A1,"."," "),7,6),"000")& TEXT(MID(SUBSTITUTE(A1,"."," "),13,9),"000")& TEXT(RIGHT(SUBSTITUTE(A1,"."," "),3),"000")),0)) -- Jacob (MVP - Excel) "Deen" 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 |
Vlookup Question
Hi jacob,
Thanks for your great help, Is look good, But i'm confused with formula, Could you please help me to how i can do in macro. Thanks in advance Deen "Jacob Skaria" wrote: Its pretty easy to do this using a macro..If you are looking for a formula try the below (bit lengthy) In Result sheet cell A1 enter the IP number In cell B1 enter the below formula. Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" The result for the sample you pasted would be as below (which is different from what you have posted) 192.170.30.30 AG 194.168.10.20 AG 10.131.37.20 AP 10.170.255.255 #N/A 10.129.36.8 AP =INDEX('IP Range'!$C$1:$C$10, MATCH(1,(TEXT(LEFT(SUBSTITUTE('IP Range'!$A$1:$A$10,"."," "),3),"000")& TEXT(MID(SUBSTITUTE('IP Range'!$A$1:$A$10,"."," "),7,6),"000")& TEXT(MID(SUBSTITUTE('IP Range'!$A$1:$A$10,"."," "),13,9),"000")& TEXT(RIGHT(SUBSTITUTE('IP Range'!$A$1:$A$10,"."," "),3),"000")<= TEXT(LEFT(SUBSTITUTE(A1,"."," "),3),"000")& TEXT(MID(SUBSTITUTE(A1,"."," "),7,6),"000")& TEXT(MID(SUBSTITUTE(A1,"."," "),13,9),"000")& TEXT(RIGHT(SUBSTITUTE(A1,"."," "),3),"000"))*( TEXT(LEFT(SUBSTITUTE('IP Range'!$B$1:$B$10,"."," "),3),"000")& TEXT(MID(SUBSTITUTE('IP Range'!$B$1:$B$10,"."," "),7,6),"000")& TEXT(MID(SUBSTITUTE('IP Range'!$B$1:$B$10,"."," "),13,9),"000")& TEXT(RIGHT(SUBSTITUTE('IP Range'!$B$1:$B$10,"."," "),3),"000")= TEXT(LEFT(SUBSTITUTE(A1,"."," "),3),"000")& TEXT(MID(SUBSTITUTE(A1,"."," "),7,6),"000")& TEXT(MID(SUBSTITUTE(A1,"."," "),13,9),"000")& TEXT(RIGHT(SUBSTITUTE(A1,"."," "),3),"000")),0)) -- Jacob (MVP - Excel) "Deen" 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 |
Vlookup Question
Hi Jacob,
Sorry for delay response, Is not working, "Jacob Skaria" wrote: Its pretty easy to do this using a macro..If you are looking for a formula try the below (bit lengthy) In Result sheet cell A1 enter the IP number In cell B1 enter the below formula. Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" The result for the sample you pasted would be as below (which is different from what you have posted) 192.170.30.30 AG 194.168.10.20 AG 10.131.37.20 AP 10.170.255.255 #N/A 10.129.36.8 AP =INDEX('IP Range'!$C$1:$C$10, MATCH(1,(TEXT(LEFT(SUBSTITUTE('IP Range'!$A$1:$A$10,"."," "),3),"000")& TEXT(MID(SUBSTITUTE('IP Range'!$A$1:$A$10,"."," "),7,6),"000")& TEXT(MID(SUBSTITUTE('IP Range'!$A$1:$A$10,"."," "),13,9),"000")& TEXT(RIGHT(SUBSTITUTE('IP Range'!$A$1:$A$10,"."," "),3),"000")<= TEXT(LEFT(SUBSTITUTE(A1,"."," "),3),"000")& TEXT(MID(SUBSTITUTE(A1,"."," "),7,6),"000")& TEXT(MID(SUBSTITUTE(A1,"."," "),13,9),"000")& TEXT(RIGHT(SUBSTITUTE(A1,"."," "),3),"000"))*( TEXT(LEFT(SUBSTITUTE('IP Range'!$B$1:$B$10,"."," "),3),"000")& TEXT(MID(SUBSTITUTE('IP Range'!$B$1:$B$10,"."," "),7,6),"000")& TEXT(MID(SUBSTITUTE('IP Range'!$B$1:$B$10,"."," "),13,9),"000")& TEXT(RIGHT(SUBSTITUTE('IP Range'!$B$1:$B$10,"."," "),3),"000")= TEXT(LEFT(SUBSTITUTE(A1,"."," "),3),"000")& TEXT(MID(SUBSTITUTE(A1,"."," "),7,6),"000")& TEXT(MID(SUBSTITUTE(A1,"."," "),13,9),"000")& TEXT(RIGHT(SUBSTITUTE(A1,"."," "),3),"000")),0)) -- Jacob (MVP - Excel) "Deen" 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 |
| All times are GMT +1. The time now is 12:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com