Comparing columns and extracting data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

thermometer - 15 Nov 2007 23:27 GMT

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.

Reply to this Message

T. Valko - 16 Nov 2007 04:37 GMT

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$2:D2)))

> 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)))

Signature

Biff
Microsoft Excel MVP

> I've got two columns with lots of data in each. Some unique, some the
> same. Short example:
[quoted text clipped - 26 lines]
>
> Any help is appreciated.

Reply to this Message

Rate this thread: