Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering Text Data from Multiple columns | Excel Worksheet Functions | |||
Text To Columns | Excel Worksheet Functions | |||
How do I compare two columns on seperate sheets and replace text . | Excel Worksheet Functions | |||
Text to columns will not shut off. | Excel Worksheet Functions | |||
Why text to columns is not available when there is mulipule line . | Excel Worksheet Functions |