Forum Discussion

AdamH975's avatar
AdamH975
Copper Contributor
Jan 16, 2022

Formula help - multiple cells matching and returning an output whilst ignoring blanks

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.

excel
Formulas and Functions

3 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jan 16, 2022

    AdamH975 

    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
    )
  )
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 16, 2022

    AdamH975 

     

    =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.

Resources