Re: Finding Duplicated Text within Columns
From: Jason Morin (jasonjmorin_at_OPPOSITEOFCOLDmail.com)
Date: 02/15/05
- Next message: BobT: "Re: Can I reference =, <, or > sign in SUMPRODUCT"
- Previous message: n.almeida: "override language settings on date"
- In reply to: Jen: "Finding Duplicated Text within Columns"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 15 Feb 2005 06:43:55 -0800
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
>.
>
- Next message: BobT: "Re: Can I reference =, <, or > sign in SUMPRODUCT"
- Previous message: n.almeida: "override language settings on date"
- In reply to: Jen: "Finding Duplicated Text within Columns"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
