find duplicate cases across multiple columns that are randomly ordered

Hi all,

Your help would be much appreciated with the following:

I have one column (A) with values of which some are duplicates (ranging from 1-4 duplicates per value). These values each have corresponding values in B.

I would like to have a formula which shows me which cases have completely matching A and B columns (consistent; so all the duplicate cases are exactly the same across A and B) and which have differing A,B values (inconsistent).

I'm thinking something like a combo of AND, VLOOKUP/ INDEX,MATCH

Hopefully the attached file makes it clear.

Thank you!

This shoud do:

=IF(SUMPRODUCT(--(TRIM($A$2:$A$15)=TRIM(A2)),--(TRIM($B$2:$B$15)=TRIM(B2)))=2,"Consistent","Inconsistent")

But in your sample, you have two rows of data mapped as Inconsistent, rows 9 and 11, even if these should fall under Consistent because 2 values are present, not one or more. Why?

Thank you for the reply.

I don't quite understand how sumproduct can apply to text strings but it does seems to work when I apply it. I will try to dissect the formula to really understand it.

The input in rows 9 and 11 was a mistake on my part.

Please mark the Thread as Solved from Thread Tools if that took care of your problem. Thanks.