How to see if all text in each column match each other but do not take blanks into consideration

[alethea2022]

i would like a formula in column g that i can autofilter down that says if a1, b1, c1, d1, e1, f1 are the same as each other let me know in some way.

I have used the and if feature but because there are some cells with blank data it classes this as not a match and i want them to be a match. row 2 would of cause be a no match thank you

 

[DanteAmor]

In G1 and copy down

=IF(COUNTIF(A1:F1,A1)=COUNTA(A1:F1),"Equals","Not")

 

[Joe4]

Are the blanks always at the end, like you show in row 1, or could they be interspersed?
If column A will ALWAYS have data, then Dante's formula should work.
If, for any row, column A may be blank while other columns have data, that formula will need some editing.

 

[GuzmanServices]

As Joe4 mentioned, you may have to use this formula for all possible outcomes:

=IF(COUNTIF(A1:F1,A1)=COUNTA(A1:F1),"Equals",IF(COUNTIF(B1:F1,B1)=COUNTA(B1:F1),"Equals",IF(COUNTIF(C1:F1,C1)=COUNTA(C1:F1),"Equals",IF(COUNTIF(D1:F1,D1)=COUNTA(D1:F1),"Equals",IF(COUNTIF(E1:F1,E1)=COUNTA(E1:F1),"Equals",IF(COUNTIF(F1:F1,F1)=COUNTA(F1:F1),"Equals","Not"))))))

 

[Joe4]

As Joe4 mentioned, you may have to use this formula for all possible outcomes:

=IF(COUNTIF(A1:F1,A1)=COUNTA(A1:F1),"Equals",IF(COUNTIF(B1:F1,B1)=COUNTA(B1:F1),"Equals",IF(COUNTIF(C1:F1,C1)=COUNTA(C1:F1),"Equals",IF(COUNTIF(D1:F1,D1)=COUNTA(D1:F1),"Equals",IF(COUNTIF(E1:F1,E1)=COUNTA(E1:F1),"Equals",IF(COUNTIF(F1:F1,F1)=COUNTA(F1:F1),"Equals","Not"))))))

That does not seem to work.
It seems to return "EQUALS" for me in ALL instances, regardless of data content. Issues are highlighted in yellow:

 

[Joe4]

Here is a variation of Dante's original formula that addresses the example I show on row 2 of my last post:

=IF(COUNTIF(A1:F1,XLOOKUP(TRUE,NOT(ISBLANK(A1:F1)),A1:F1))=COUNTA(A1:F1),"Equals","Not")

 

[DanteAmor]

If, for any row, column A may be blank while other columns have data, that formula will need some editing.

Try:

=LET(a,A2:F2,SI(COUNTA(a)=COUNTIF(a,INDEX(a,,LARGE((a<>"")*COLUMN(a),1))),"Equals","Not"))

 

[Joe4]

=LET(a,A2:F2,SI(COUNTA(a)=COUNTIF(a,INDEX(a,,LARGE((a<>"")*COLUMN(a),1))),"Equals","Not"))

I think that "SI" needs to be converted to an "IF" for non-Spanish users of Excel.
When I made that change, it works as expected.

 

[DanteAmor]

aggghhh my translation

Sorry about that, here is my formula with the correct translation. Thanks Joe

=LET(a,A2:F2,IF(COUNTA(a)=COUNTIF(a,INDEX(a,,LARGE((a<>"")*COLUMN(a),1))),"Equals","Not"))

 

[DanteAmor]

Here another way:

=IF(COUNTA(UNIQUE(FILTER(A1:F1,A1:F1<>""),TRUE))=1,"Equals","Not")