Finding Duplicated Text within Columns

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

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



>-----Original Message-----
>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
>.
>