Traceroute identification

[danicam001]

Hello Dear team

Hi everyone, this is my first post! I'm working on figuring out the hops in an internal traceroute using a list of known IPs. I'm running a traceroute on my Windows computer, saving it to a file, and then opening it in Excel.
I want to find the hops I already know. For example, in this traceroute, some hops are part of my network, so I can recognize them easily.

Normal Traceroute
Tracing route to 10.142.111.1 over a maximum of 8 hops

1 5 ms * 7 ms 10.111.111.10
2 5 ms 5 ms 5 ms 10.111.113.20
3 5 ms 5 ms 6 ms 192.16.100.103
4 8 ms 5 ms 4 ms 192.17.143.212
5 * * * Request timed out.
6 11 ms 22 ms 9 ms 192.142.120.1

Trace complete.

What I want to achieve is to have a list of all IPs that I know, either in another sheet or the same one. For those IPs that I recognize, place the corresponding names on the right side of the tracert, like this:

List of known Ips:
10.111.113.20 - router123
192.16.100.103 - router456
192.17.143.212 - router777
192.142.120.1 - Core1
etc

The final result I would like to be like this:
Tracing route to 10.142.111.1 over a maximum of 8 hops

Column A Column B
1 5 ms * 7 ms 10.111.111.10
2 5 ms 5 ms 5 ms 10.111.113.20 router123
3 5 ms 5 ms 6 ms 192.16.100.103 router456
4 8 ms 5 ms 4 ms 192.17.143.212 router777
5 * * * Request timed out.
6 11 ms 22 ms 9 ms 192.142.120.1 Core1

Trace complete.

Thank you, Dan

 

[danicam001]

Just improving the formatting this is the expected result:

ColumnA	Column B
Tracing route to 10.142.111.1 over a maximum of 8 hops
1 5 ms * 7 ms 10.111.111.10	router123
2 5 ms 5 ms 5 ms 10.111.113.20	router456
3 5 ms 5 ms 6 ms 192.16.100.103	router777
4 8 ms 5 ms 4 ms 192.17.143.212
5 * * * Request timed out.
6 11 ms 22 ms 9 ms 192.142.120.1	Core1

 

[Peter_SSs]

Welcome to the MrExcel board!

Would something like this help?

23 12 11.xlsm
	A	B	C	D	E
1	Normal Traceroute			List of known Ips:
2	Tracing route to 10.142.111.1 over a maximum of 8 hops			10.111.113.20	router123
3				192.16.100.103	router456
4	1 5 ms * 7 ms 10.111.111.10			192.17.143.212	router777
5	2 5 ms 5 ms 5 ms 10.111.113.20	router123		192.142.120.1	Core1
6	3 5 ms 5 ms 6 ms 192.16.100.103	router456
7	4 8 ms 5 ms 4 ms 192.17.143.212	router777
8	5 * * * Request timed out.
9	6 11 ms 22 ms 9 ms 192.142.120.1	Core1
Traceroute

Cell Formulas
Range	Formula
B4:B9	B4	=IFNA(VLOOKUP(TEXTAFTER(A4," ",-1),D$2:E$5,2,0),"")

 

[danicam001]

Thank you very much. It worked like a charm!!
I was also trying to do it via vba.

I need to add a second for loop for the "list of known IPs" and control

Sub find_in_traceroute()
Last = Cells(Rows.Count, "b").End(xlUp).Row
For i = 1 To 500
    If (Cells(i, "b").Value) Like "*172.17.10.10*" Then
        Cells(i, "c").Value = "ROUTER-ABC"
    End If
Next i
End Sub

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Thank you very much. It worked like a charm!!

Good news!

I was also trying to do it via vba.

In future, please include such information in your original post.

Your code above calculates a value for 'Last' but then does not use that value anywhere. Should it?

Where is your list of known IPs and the corresponding names?