Verify that multiple cells have the same value

[msvec]

Aside from using the EXACT function, or other stacked IF statements, is there a function that allows you to verify that multiple cells all have the same value? Example would be if A1='Dog', A2='Dog', A3='Dog', then i'd like some function to return TRUE or FALSE if they were indeed all the same, like MULTIEXACT(A1,A2,A3).

thanks

 

[msvec]

in case anyone cares, or cares to offer up a better solution, i came up with this for my example:

=IF(COUNTIF(A1:A3,A1)=COUNTA(A1:A3),TRUE,FALSE)

seems to work OK.

which could be shaved down to this:

=COUNTIF(A1:A3,A1)=COUNTA(A1:A3)

 

[Aladin Akyurek]

=AND(EXACT(A1:A3,A1))

which must be confirmed with control+shift+enter instead of just with enter.

 

[Aladin Akyurek]

in case anyone cares, or cares to offer up a better solution, i came up with this for my example:

=IF(COUNTIF(A1:A3,A1)=COUNTA(A1:A3),TRUE,FALSE)

seems to work OK.

which could be shaved down to this:

=COUNTIF(A1:A3,A1)=COUNTA(A1:A3)

That wouldn't distinguish between Dog and DOG.

 

[msvec]

i'm not sure whta ctrl,shift,enter does b/c it worked ok w/ just enter (i'm editing this b/c i figured out that ctrl,shift,enter forces an array and compares all values in the range, so i get that part now), however, i've run into another problem. if the cells to be verified aren't in a contiguous range, neither of our formulas works. i'm stumped again.

 

[maxflia10]

Non-array using Morefunc,

=IF(COUNTDIFF(A1:A3)=1,TRUE,FALSE)

 

[msvec]

sorry for the barrage of posts, but the sheet i'm working with is terrible, and i figured by the time i created a formula to compare all the 'random' cells, i may as well pull all the values i must verify into a contiguous range and then run either the formula i included earlier or Alaydn's solution, unless someone has a better idea.

 

[Aladin Akyurek]

sorry for the barrage of posts, but the sheet i'm working with is terrible, and i figured by the time i created a formula to compare all the 'random' cells, i may as well pull all the values i must verify into a contiguous range and then run either the formula i included earlier or Alaydn's solution, unless someone has a better idea.

Control+shift+enter:

=AND(EXACT(ArrayUnion(A1,B2,C4),A1))

The code for ArrayUnion is:

Option Explicit

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: http://makeashorterlink.com/?P20022174
' Mod: Nov 3, 2003, to reduce number of ReDim Preserve calls.
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
ReDim TempUnion(1 To UBound(Arg) - LBound(Arg) + 1) As Variant
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
If Ctr > UBound(TempUnion) Then
ReDim Preserve TempUnion(1 To UBound(TempUnion) * 2) As Variant
End If
'ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
If Ctr > UBound(TempUnion) Then
ReDim Preserve TempUnion(1 To UBound(TempUnion) * 2) As Variant
End If
'ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
If Ctr < UBound(TempUnion) Then
ReDim Preserve TempUnion(1 To Ctr) As Variant
End If
ArrayUnion = TempUnion
End Function

 

[msvec]

wow, that's out of my league, but it works quite nicely, and is, of course, case sensitive. you can even through a range in the middle of it. thanks, i'll use it.

thanks Aladin

take care.

 

[dawn12789]

Can anyone help me,,, when im trying to use your formula it shows this message...

We found a problem with this formula. Try clicking Insert Function on the Formula tab to fix it, or click Help for more info on common formula problems.

Not trying to type a formula?
When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula...


You type = 1+1, cell shows: 2


To get around this, type an apostrophe (') first:
you type : '=1+1, cell shows:  = 1 +1