How to compare text across multiple columns

[Anonymous]

Hi,

How do i compare text across multiple columns ignoring any blanks and ignoring case.

e.g.

Column1	Column2	Column3	Column4	Column5	Expected Result
cat	cat	cat	cat	cat	TRUE
cat	Cat	CAT	cat	cat	TRUE
	cat	cat			TRUE
cat	cat	cater	cat	cat	FALSE

In Excel, i used the following formula and it worked fine:

=SUMPRODUCT((A2:E2<>"")/COUNTIF(A2:E2,A2:E2&""))=1

Thanks for any help.

[lbendlin · Accepted solution]

Result =

var a= {[Column1],[Column2],[Column3],[Column4],[Column5]}

var b=FILTER(a,[Value]<>"")

return COUNTROWS(DISTINCT(b))=1

or more brutish:

Result = COUNTROWS(FILTER(DISTINCT({[Column1],[Column2],[Column3],[Column4],[Column5]}),[Value]<>""))=1

[Anonymous]

Both Solutions work.  Thank You so much.

[lbendlin]

here's a fun way:

= SWITCH(TRUE(),[Column1]<>[Column2],FALSE(),[Column1]<>[Column3],FALSE(),[Column1]<>[Column4],FALSE(),[Column1]<>[Column5],FALSE(),TRUE())

[Anonymous]

Doesn't seem to work if there are any blank columns.