Forum Discussion
How to compare text across multiple columns
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.
- Result =var a= {[Column1],[Column2],[Column3],[Column4],[Column5]}var b=FILTER(a,[Value]<>"")return COUNTROWS(DISTINCT(b))=1or more brutish:Result = COUNTROWS(FILTER(DISTINCT({[Column1],[Column2],[Column3],[Column4],[Column5]}),[Value]<>""))=1
4 Replies
- lbendlin
Super User
Result =var a= {[Column1],[Column2],[Column3],[Column4],[Column5]}var b=FILTER(a,[Value]<>"")return COUNTROWS(DISTINCT(b))=1or more brutish:Result = COUNTROWS(FILTER(DISTINCT({[Column1],[Column2],[Column3],[Column4],[Column5]}),[Value]<>""))=1 - lbendlin
here's a fun way:
= SWITCH(TRUE(),[Column1]<>[Column2],FALSE(),[Column1]<>[Column3],FALSE(),[Column1]<>[Column4],FALSE(),[Column1]<>[Column5],FALSE(),TRUE())