Finding Duplicated Text within Columns

[Jen]

I'd be grateful if anyone could help me with the following:

I have 2 columns, both contain a number of items, some the same, some not; eg:


Column A: Column B:
Row 1: apple pear orange apple orange
Row 2: orange banana banana orange
Row 3: peach banana melon grape apple orange

I need to compare the columns and find which rows are NOT exactly the same
and, preferably, to identify the differences.

For instance, in the above example, I would ignore Row 2 as the cells
contain the same items (albeit in a different order), but I want Excel to
identify in the next Column items which are not the same, eg in Row 1 it
would identify "pear" etc.

I have tried various ways of solving this to no avail! I would prefer to
not have to use a pivot table in this instance.

Many thanks

Jen

 

[Jason Morin]

While there is a chance this formula will not always be
100% accurate, it should work in your case. In C1 put
(all 1 formula):

=SUMPRODUCT(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))
=SUMPRODUCT(CODE(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)))

and fill down. FALSE indicates that the cells of the same
row in columns A an B are not the same.

HTH
Jason
Atlanta, GA