# check if multiple text cells are equal

#### za3pola3

##### New Member
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>

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
See if this works for you,

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

I'm not really good with excel formulas. I'm a VBA guy myself. I wrote a program for you. Here is the code.
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.
Code:
``=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.
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

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.
Code:
``=IF(SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))=1,TRUE,FALSE)``

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

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

Replies
5
Views
210
Replies
8
Views
222
Replies
1
Views
117
Replies
0
Views
145
Replies
2
Views
92

1,220,980
Messages
6,157,189
Members
451,404
Latest member
Probe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back