check if multiple text cells are equal

[za3pola3]

what is formula can i use to check if multiple text cells in in range A1:A10 are equal and ignore blank cell (like this

x	Y	x
x	Y	x	x		Y	X
x	Y	x
x	Y
x	Y	x
x	Y	Y		Y
x	Y		x
x	Y	Y
x	Y	Y
x	Y	Y		Y
TRUE	TRUE	FALSE	TRUE	TRUE	TRUE	TRUE

<tbody>
</tbody>

 

[Brian from Maui]

See if this works for you,

=IF(SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))=1,"yes","no")

 

[WarPigl3t]

I'm not really good with excel formulas. I'm a VBA guy myself. I wrote a program for you. Here is the code.

Function myFunction(rng As Excel.Range)
    stng = ""
    bool = 0
    For Each cell In rng
        If cell <> "" Then
            stng = cell
            bool = bool + 1
        End If
        If bool = 1 Then
            setOutput = stng
        End If
        If stng <> setOutput Then
            myFunction = False
            Exit Function
        End If
    Next cell
    myFunction = True
End Function

After you insert this code into your VBA macros, you can insert the following formula into your table.

=myFunction(A1:A10)

I noticed you have some lowercase and some uppercase letters in your dataset. My code doesn't consider a lowercase letter to match an uppercase letter. So if x and X are in the same column, it will display FALSE. If you would rather it not be case sensitive, use this code.

Function myFunction(rng As Excel.Range)
    stng = ""
    bool = 0
    For Each cell In rng
        cell = LCase(cell)
        If cell <> "" Then
            stng = cell
            bool = bool + 1
        End If
        If bool = 1 Then
            setOutput = stng
        End If
        If stng <> setOutput Then
            myFunction = False
            Exit Function
        End If
    Next cell
    myFunction = True
End Function

If you don't know how to insert code into your VBA macros. Follow this link. How to Write Excel Macros via VBA - YouTube

 

[WarPigl3t]

See if this works for you,

=IF(SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))=1,"yes","no")

Brian's way is easier. It is not case sensitive though. So x is the same as X.

=IF(SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))=1,TRUE,FALSE)

 

[Scott Huish]

If you're just going to return TRUE or FALSE, you can drop the IF entirely:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))=1

 

[Brian from Maui]

If you're just going to return TRUE or FALSE, you can drop the IF entirely:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))=1

Thanks for the correction