Compare columns and return matching text VBA

Hello,

I'm working on comparing a large string of text against another string of text and outputting any matching values in another column. I've only been able to compare the two and return whether it's a match or not or even highlight the differences (not exactly accurate). Another idiosyncrasy is that each cell contains a listing of individual names which are concatenated and separated by a delimiter (semicolon in this case), I'd like to avoid parsing these out individually, as my end goal is to have any matching names in that format separated by a semicolon.


Column 1: Jack Johnson;Tom Jackson;Josh Hazard;Tom Petty;John Kowalski
Column 2: Tom Petty;Charles Smith;John Terry;Steven Gerrard;Tom Jones;Nick Jones;Michael Schumacher;George Willis;Donald Simson;Jack Johnson
Output Column:Tom Petty;Jack Johnson

Another issue I've ran into is that there is overlap in some of the names (e.g., the Jack in Tom Jackson comes back as a match for the Jack in Jack Johnson). Is there any way to treat each name individually based on the delimiter?

Any help or suggestions would be greatly appreciated.

Please see attached.

This is brilliant! I tried setting it up as a loop (my actual data set is exponentially larger), but I'm getting a subscript out of range error. I need this to run 68 times across the set of 2 columns & output in the 3rd column each time (columns are all next to each other). Is there any way to set it up to run a loop through those instead of defining each specific range?

Set r = .Range("b2:D" & lr)
Set r = .Range("e2:G" & lr)
Set r = .Range("h2:J" & lr)
.
.
.
Set r = .Range("gu2:GW" & lr)