Return if all Excel column values ​​do not match

Question

I have the following data in an Excel spreadsheet:

ABC 1 bbb 

I would like to put the formula in the column after the data that compares each (text) value in the row and returns TRUE if they are all the same, i.e. A = B = C. I would like it to return FALSE if one or more of the values ​​do not match. i.e

  ABCD 1 bbb TRUE 2 beb FALSE 3 ebb FALSE 

I know logical functions like AND, so I could build something like

AND(A1=B1,A1=C1.. etc) , however this soon becomes cumbersome as the number of columns increases.

Data restructuring is not performed.

Does anyone know an OOTB / VB solution?

Answer 1

If countif finds the same number as count , then they are all the same.

 =IF(COUNTIF(A1:C1,A1)=COUNTA(A1:C1),"true","false") 

Hope this is what you are looking for, you just need to expand the ranges for the number of columns you want to check.

Update

As indicated in the comment, this does not allow returning the correct result if the data set has empty cells.

This will return false, even if there is an empty cell in the range:

 =IF(AND(COUNTIF(A1:C1;A1)=COUNTA(A1:C1);COUNTBLANK(A1:C1)=0);"true";"false") 

Answer 2

Here is the VBA UDF: to make it case sensitive remove the Compare Text option

 Option Explicit Option Compare Text Public Function AllSame(theRange As Range) As Boolean Dim vR As Variant Dim vC As Variant AllSame = True vR = theRange If IsArray(vR) Then vC = vR(1, 1) For Each vC In vR If vC <> vR(1, 1) Then AllSame = False Exit For End If Next vC End If End Function 

Answer 3

Although this will be a bit slower, you can also use the array formula (by pressing Ctrl + Shift + Enter after entering it into the cell):

 {=IF(SUM(COUNTIF(A1:C1,A1:C1))=POWER(COUNTA(A1:C1), 2),"true","false")} 

This will work even if there are empty cells in the range.

.

UPDATE:
This array formula also works, making sure that there is only one unique value in the range. It also still returns the exact value, even if there are spaces in the space:

 {=SUM(IFERROR(1 / COUNTIF(A2:C2,A2:C2), 0)) = 1}