Automatic find difference between two data

[Kuannygohcheetatt]

Hey guys, my supervisor ask me to do one thing, he want me create a template where he only need to copy paste the first series of data in sheet 1 ,the second series of data in sheet two and when he click sheet three, it will display all data that are similar between sheet 1 and 2 and when he click sheet 4 it will display all data that are different. Any idea how to do it? I tried v look up but fail.

Sheet 1 data 1,2,3,4,5


Sheet 2 data 1,2,3,4,5,6,7
Sheet3 data 1,2,3,4,5
Sheet 4 data 6,7


Or another example
Sheet 1 cat, hat, dog
Sheet two cat ,pork, tom
Sheet three cat
Sheet 4 hat dog pork tom

[gebobs]

Are there ever any repeats in either list? e.g. cat, hat, dog, cat

Is the complete set of terms known regardless if they are one, both, or neither list?

[gebobs]

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.