Comparing 2 text columns and extracting their differences to a third

Posted by Chris Woodward on July 14, 2000 7:50 PM

Hi all, nice to discover this site, as I have a question that has befuddled me for some time. Mind you, I'm a bit of an Excel amateur - I imagine there is a very simple equation for this, but I've had no luck in finding it.

I have 2 text columns, and they're both rather long. The columns share many of the exact same words (punctuation & all), but there are some words that are particular to a single column. I want to know what those words are (the differences) w/o having to manually sort through the two columns. Is there something that will allow me to compare these 2 columns and then insert copies of the "uncommon" words from one column or the other into a seperate space?

Thanks for any help,

Chris

Posted by Ada on July 16, 0100 1:09 AM

Here's one way

Chris
Here's one way of doing it :-

In cell B1 enter this formula and drag it down as far as required:
=IF(ISNA(VLOOKUP(A1,C:C,1,FALSE)),A1,"")
The data in column A which does not appear in column C should appear in column B.

In cell D1 enter this formula and drag it down as far as required:
=IF(ISNA(VLOOKUP(C1,A:A,1,FALSE)),C1,"")
The data in column C which does not appear in column A should appear in column D.

Ada

Posted by Chris Woodward on July 17, 0100 8:10 AM

Re: Here's one way

Ada-

Fantastic! That works great.. thanks very much for your help, this will save me hours of work.

Cheers,

Chris

Posted by Chris Woodward on July 15, 0100 11:33 AM

Ok, a very simple example:

A1->A4 contains:
sailboat
tax evasion
carburator
pot roast

C1->C8 contains:
submarine
lilies
tax evasion
carburator
sailboat
orthopedic shoes
pot roast
basketball
particle board

So, in this example, there's one column whose contents are contained in their entirety in the other column.
What I want to do is enter a function in Excel to copy those words not common to both columns (such as in this case "particle board") to a separate space.
Anyone know of something that would work? It's not so important that I have the uncommon expressions copied, so much as that I'm alerted to which expressions are not shared.

Thanks again for any help,

Chris

Posted by Ada on July 14, 0100 11:04 PM

Chris
It would be helpful if you could post some sample data.
Ada