Comparing columns and extracting data

[thermometer]

I've got two columns with lots of data in each. Some unique, some the
same. Short example:
Col A
cat
cow
dolphin
dog
fish
horse
snake
zebra

Col B
bear
cow
dolphin
dog
fish
hamster
monkey
zebra

I would like to create additional columns with the following
stipulations:

Col C (what's common in both A & B)
Col D (what's in A but not B)
Col E (what's in B but not A)

Any help is appreciated.

 

[T. Valko]

These are all array formulas. **Array formulas need to be entered using the
key combination of CTRL,SHIFT,ENTER (not just ENTER)

Col C (what's common in both A & B)

Assuming your data starts on row 2. Enter this array formula** in C2 and
copy down until you get #NUM! errors meaning all data has been exhausted:

=INDEX(A$2:A$9,SMALL(IF(ISNUMBER(MATCH(A$2:A$9,B$2:B$9,0)),ROW(A$2:A$9)-MIN(ROW(A$2:A$9))+1),ROWS(C$2:C2)))

Col D (what's in A but not B)

Assuming your data starts on row 2. Enter this array formula** in D2 and
copy down until you get #NUM! errors meaning all data has been exhausted:

=INDEX(A$2:A$9,SMALL(IF(ISNA(MATCH(A$2:A$9,B$2:B$9,0)),ROW(A$2:A$9)-MIN(ROW(A$2:A$9))+1),ROWS(D$22)))

Col E (what's in B but not A)

Assuming your data starts on row 2. Enter this array formula** in E2 and
copy down until you get #NUM! errors meaning all data has been exhausted:

=INDEX(B$2:B$9,SMALL(IF(ISNA(MATCH(B$2:B$9,A$2:A$9,0)),ROW(A$2:A$9)-MIN(ROW(A$2:A$9))+1),ROWS(E$2:E2)))