Match part of or all of hostname or ip-address between 2 tables

[kevinbernard]

Where to start, to perform a match on part of a string?
I want to match either column in Table1 (hostname OR IP-address), to anything in Table2.

Table1 is output from my network tool. It's good data all formated nicely, and correct.
Table2 is list of servers and/or their corresponding IP address, and not so neat.

do i need a combination tools to accomplish? regex, spit,find-replace?

table1		Table2
hostname1	IP-address1		hostname2	IP-address2	owner
server1	192.168.1.1		server1	192.168.1.1	joe
server2	192.168.1.2		server2.corp.com	192.168.1.2	bill
server3	192.168.1.3			192.168.1.3	juan
server4	192.168.1.4		server4-new	192.168.1.4	betty
server5	192.168.1.5		server5	192.168.1.5	marie
server6	192.168.1.6		192.168.1.6		joblow
server7	192.168.1.7		server7	192.168.1.7	them

output something like this...
hostname1, IP address1, hostname2, owner

Thanks in advance for your input!

[jarrod · Accepted solution]

I would do a waterfall first joining on IP-address1 from Table1 to IP-Address2 from Table2, then follow that up with a second join using IP-Address1 from Table1 and HostName2 from Table2.

However, I think you are getting at something a little more complex, in which case, i would assign a value to the records, transpose, join all records and find the combinations. See my attached workflow - i like transpose and join so far, although all 3 will work relatively well.

[kevinbernard]

Hi thanks for going above and giving 3 solutions. Waterfall is one idea i somewhat understood could help so i will plan to use that one. Thanks again for saving me 8 hour of reial and error. Love the answers.
Kevin