Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell next to another cell with specific value
Hi I need to know how I can do the above.
Example I have a list of Ip Address 10.70.60.121 , 10.70.60.125 , 10.70.10.50 etc Now What I want to do is using a Macro if posiible add a cell next the the one containing the IP address which will give me the location that I will specify it should be . Reason for this is we get a lot of lists with Ip address and we can only see where these IP belong by manually searching the Gateways and then typing the Sites in the next Colums next to the Ip colums .. will make my life easier if I can run a Macro that can do this for me ... Any help will be appreciated. Regards Gerhard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell next to another cell with specific value
Gerhard,
Easier, I think, would be to make a list of IP addresses and locations, then use VLOOKUP formulas to extract that data. Your list could be expanded as needed. Also, it could be based on all or part of the IP. Something like this to find the entire address: =VLOOKUP(A2,IPData!A:B,2,False) Or, based on the first three numbers: =VLOOKUP(LEFT(A2,FIND(".",A2,FIND(".",A2,FIND(".", A2)+1)+1)-1),IPData!A:B,2,False) First two numbers: =VLOOKUP(LEFT(A3,FIND(".",A3,FIND(".",A3)+1)-1),IPData!A:B,2,False) First number only: =VLOOKUP(LEFT(A4,FIND(".",A4)-1),IPData!A:B,2,False) where your data table is on a sheet named IPData, in columns A and B. HTH, Bernie MS Excel MVP "Gerhard Kriek" <Gerhard wrote in message ... Hi I need to know how I can do the above. Example I have a list of Ip Address 10.70.60.121 , 10.70.60.125 , 10.70.10.50 etc Now What I want to do is using a Macro if posiible add a cell next the the one containing the IP address which will give me the location that I will specify it should be . Reason for this is we get a lot of lists with Ip address and we can only see where these IP belong by manually searching the Gateways and then typing the Sites in the next Colums next to the Ip colums .. will make my life easier if I can run a Macro that can do this for me ... Any help will be appreciated. Regards Gerhard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding data to a cell next to another cell with specific value
Look at this:
http://www.contextures.com/xlFunctions03.html This is worth a look too: http://www.ozgrid.com/VBA/ExcelRanges.htm Finally, this is very good! http://www.rondebruin.nl/find.htm HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Bernie Deitrick" wrote: Gerhard, Easier, I think, would be to make a list of IP addresses and locations, then use VLOOKUP formulas to extract that data. Your list could be expanded as needed. Also, it could be based on all or part of the IP. Something like this to find the entire address: =VLOOKUP(A2,IPData!A:B,2,False) Or, based on the first three numbers: =VLOOKUP(LEFT(A2,FIND(".",A2,FIND(".",A2,FIND(".", A2)+1)+1)-1),IPData!A:B,2,False) First two numbers: =VLOOKUP(LEFT(A3,FIND(".",A3,FIND(".",A3)+1)-1),IPData!A:B,2,False) First number only: =VLOOKUP(LEFT(A4,FIND(".",A4)-1),IPData!A:B,2,False) where your data table is on a sheet named IPData, in columns A and B. HTH, Bernie MS Excel MVP "Gerhard Kriek" <Gerhard wrote in message ... Hi I need to know how I can do the above. Example I have a list of Ip Address 10.70.60.121 , 10.70.60.125 , 10.70.10.50 etc Now What I want to do is using a Macro if posiible add a cell next the the one containing the IP address which will give me the location that I will specify it should be . Reason for this is we get a lot of lists with Ip address and we can only see where these IP belong by manually searching the Gateways and then typing the Sites in the next Colums next to the Ip colums .. will make my life easier if I can run a Macro that can do this for me ... Any help will be appreciated. Regards Gerhard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing | Excel Discussion (Misc queries) | |||
how do i find specific data in a cell and its colocated cell data | Excel Worksheet Functions | |||
Send data from userform to specific cell on specific sheet? | Excel Programming | |||
Lock data in a cell a specific cell based on selection on other ce | Excel Worksheet Functions |