• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Equals function did not display True despite sting in cell 1 is the same in cell 2

Hi Guys

I have 5 different email fields; Email 1, 2, 3, 4 and 5

In row 1; I have "field1gmail.com", "field2gmail.com", "field3gmail.com", "field4gmail.com", "field5gmail.com" for Email 1, 2, 3, 4 and 5 respectively.

In row 2; I have "field5gmail.com","field4gmail.com", "field3gmail.com", "field2gmail.com", "field1gmail.com" for Email 1, 2, 3, 4 and 5 respectively.

I use concatenate to merge the 5 emails together.

The purpose is to identify whether is there any changes in email address regardless of the emails being place in different Email fields.

Therefore, it seems that if I were to use cell1(merge emails for row1) = cell2(merge emails for row2); the result become false.

Please advice what formula show I use apart from VBA.

Email 1
Email 2
Email 3
Email 4
Email 5


Peter Bartholomew

Well-Known Member
Like @bosco_yip I would test first and combine results later.
= checkCopy<>baseCopy
will give TRUE for any mismatch. Because traditional formulas on the grid screw up array formulas, unless you remember CSE, I would use a defined name 'hasError' to calculate the formula (entered in the Name Manager 'Refers to' box).
To test for the presence of errors then requires
= OR(hasError)

With dynamic array formulas I can play further tricks and return a list of email addresses that have consistency errors
= FILTER( emails, hasError, "perfect" )
but that's another story.