Jan 16 2022 08:47 AM
Hi there I am looking for some help.
I want to compare four cells on the same row to see if any combination of them do not have the same text, and then output either yes or no. I have tried a couple of things but haven't got it to work. The text is free and so conditional formatting won't be useful nor will the exact function.
Any combination of the cells could be populated However, some of these cells might be blank and I want to ignore the blank cells and only compare the cells that have text, but these cells may later be populated with information so I don't want to exclude them permanently.
For example: cells H1,K1,N1 and Q1 (all on the same worksheet)
H1 - tank or H1 - Tray or H1 - tank or H1 tank or any combo of words or blanks
K1 - 'blank' K1 - tank K1 - tank K1 tank
N1 - tray N1 - tank N1 - tank N1 'blank'
Q1 - tank Q1 - tank Q1 - tank Q1 tank
Output: 'different' Output: 'different' Output: 'same' Output: 'same'
I have tried using an array and also nested IF functions and neither of them seemed to work. Does anyone have a potential solution? Many thanks.
Jan 16 2022 09:12 AM
Jan 16 2022 11:24 AM
=IF(OR(SUM((H1<>"")+(K1<>"")+(N1<>"")+(Q1<>""))=0,SUM((H1<>"")+(K1<>"")+(N1<>"")+(Q1<>""))=SUM((H1=K1)+(H1=N1)+(H1=Q1))+1),"Same","Different")
If you don't have Microsoft 365 or Office 2021, confirm with Ctrl+Shift+Enter.
Jan 16 2022 03:56 PM
With 365 you could filter out the blanks and then use UNIQUE to return a row of distinct values. If there is only one you have a match.
= COLUMNS(UNIQUE(FILTER(line,line<>""),TRUE))=1
In future, a Lambda helper function could be used to return results for every line with a single formula
= BYROW(data,
LAMBDA(line,
COLUMNS(
UNIQUE(
FILTER(line,line<>""),
TRUE
)
) = 1
)
)