# 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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### Brian from Maui

##### MrExcel MVP
See if this works for you,

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

• za3pola3

#### WarPigl3t

##### Well-known Member
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

#### WarPigl3t

##### Well-known Member
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)``

#### Scott Huish

##### MrExcel MVP
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

• za3pola3

#### Brian from Maui

##### MrExcel MVP
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
0
Views
120
Replies
11
Views
2K
Replies
5
Views
348
Replies
4
Views
294
Replies
6
Views
405

### Forum statistics

1,195,644
Messages
6,010,891
Members
441,571
Latest member
stolenweasel ### 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