Here is a rather inelegant solution to kick off the discussion...

The "On both" tab checks each cell in sheet1 for matches in sheet2. Any cells without matches return a blank. You probably don't want blanks, but there are ways around that with a quick macro to copy, paste text, and sort. It might be useful too to show on which row of each sheet the each term is found.

The "On one" has two columns. The first checks each cell in sheet1 for no matches in sheet2. The second does the same for each cell in sheet2 with respect to sheet 1. Any cells with matches return a blank which, again, probably isn't what you want. You may not want the two columns either though this has the advantage of showing at a glance which sheet has the unique value. Again, the row identifier might be useful.

The end product I envision after the macro probably can be done via code but that's beyond my pay grade.

Attached Files

Compare.xlsx (11.7 KB, 10 views)