Assigning Source IP to Client excel function

[morai1104]

Hello, I am trying to figure out a function that allows me to assign a device to an IP address. The issue is I have 20,000 lines of data and many many IP addresses.

I can create as many devices as I please like "laptop 1" "desktop 3" "server 9" and assign a particular IP address to them. Is there a simpler way to do this, instead of manually figuring out all the IP addresses and keeping a track of what IP address is assigned to what device. 

[#2]

Just for information, do you have also over hundreds of devices or is the number of devices limited? Do you think possible to list all devices to list the devices next to the columns?
Or are you searching for any kind of nslookup integration?

Best regards
Schnittlauch

[#3]

@morai1104 

Here is an upside-down solution from (5/15/05) Rob Bovey, Created.

I found it on the internet a long time ago. Maybe it will help you.

I would be happy to know if I could help.

I wish you continued success with your Computer

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

[#4]

So there's a strong possibility that there are over 100 different Ip addresses but the good news is I can create as many devices as I want (100s even 1000s). Assigning devices makes it easier to run an analysis because people remember texts better than numbers.
How can i run the nslookup function in this situation?

[#5]

no sure how this will help. All I see is a get IP address button.

[#6]

@morai1104 

Here is a suggested solution with a data check.

If an IP is registered and already exists, a message comes up.

Exactly the same with the users, if the user is already there, a message comes up.

But that only helps if all IPs and users are entered individually from the start.

I can't quite understand your plans.

Would you like to assign the name of the PC from the specified IP? ...That's how I understood it.

How does that work? can all IPs be pinned on?

However, I recommend that you send a file (without sensitive data) and explain your plans on it.

This way, even cumbersome people like me :)) can understand your plan.

Thank you for your patience and understanding

Nikolino

[#7]

Does "Vlookup" help you?

[#8]

@NikolinoDE The Ip address and devices are not individually entered from the start in a table/chart format. Is there a way around this? If you see the attached file, you'll find there are 20,000 lines of data, and let's say for example 10% of the lines are individual IP addresses (repeated), so around 2000 unique addresses. Creating a table with 2000 IP addresses and device names will be time-consuming. 

I am going to try to convert the data into a table format, sort the ip address and then assign a device to the ip addresses. Let me know what you think?

[#9]

@morai1104 

Send you this attached file with some examples.

I found it by chance on the Internet, which I can offer you as a suggested solution.

It seems that there are two possible solutions, but the VBA solution is only to check whether the IP is active or not or IP pinging.

The possible solutions are in the three Excel sheets.

If none of the proposed solutions are suitable for you, please inform us to find another approach (whatever).

Thank you for your patience and time.

Nikolino

I know I don't know anything (Socrates)