Detecting differences between strings of text

Hi,

Please see attached example Excel file.

I have a column of values “OLD_Successors”, and want to compare them to another column of values “NEW_Successors”, and detect where there is a difference (a value that is in one field but not the other, the order does not matter).

For example, for a given Activity, one column might contain CN-113A1-MC-WP-020, CN-107B0-MC-WP-010 and the other might contain CN-110C6-MC-MT-250. I would need something to appear in my “Difference” column to indicate that the two do not contain the same values.

I need a function or formula that shows me where the two fields do not contain exactly the same values (the order of the values doesn’t matter, for my purposes “CN-113A1-MC-WP-020, CN-107B0-MC-WP-010” is exactly the same as “CN-107B0-MC-WP-010, CN-113A1-MC-WP-020”).

Basically, if there is a value in NEW_Successors that is not in OLD_Successors, or a value in OLD_Successors that is not in NEW_Successors I need a flag to alert me that I need to update my master data.

There may be up to 30 (or more) values in a cell, and they are separated by commas. Either, or both, columns may also be empty, with no values.

Thanks very much

Dave

Try ..

=IF(ISNUMBER(SEARCH("*" & C5 & "*",B5,1)),"","mismatch")

Your sample (if understand your requirement) is limited in identifying likely case. It would help if you expand the sample showing expected results in the "Difference" column.

Thanks very much John.

Dave