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

[liabilityquek]

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
field1@gmail.com	field2@gmail.com	field3@gmail.com	field4@gmail.com	field5@gmail.com	=CONCATENATE(A2,B2,C2,D2,E2)
field5@gmail.com	field4@gmail.com	field3@gmail.com	field2@gmail.com	field1@gmail.com	=CONCATENATE(A3,B3,C3,D3,E3)
					=F2=F3

 

[bosco_yip]

Check Row 2 data is as same as Row 3

In F2, enter formula :

=ISNUMBER(SUMPRODUCT(MATCH(A2:E2,A3:E3,0)))

Regards
Bosco

 

[liabilityquek]

Thanks Bosco' the formula works great!

 

[Peter Bartholomew]

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.