Determine if a Range of cells contains values that are different

I'm trying to find a way to utilize an existing  function within Excel to determine if a range of cells contains numbers or text entries that are different from one another.  Does anyone know of a function or group of functions within MS Excel 2010 that can produce this result.  I know how to do this programmatically, but it would be more advantageous if this could be done with a build in function.

Thanks in advance to anyone that can assist.
Robert CarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Please give an example of what you want to be able to do.
Robert CarmanAuthor Commented:
Attached is an example of what I would like to achieved with a function or group of functions within Excel.
Martin LissOlder than dirtCommented:
It's not built in but this User Defined Function can be used in exactly the same way.

Usage: =isTheSame(A1:A3)

where A1:A3 is the range to be checked.

Function isTheSame(r As Range) As Boolean

Dim strValue As String
Dim lngRow As Long
strValue = r.Cells(1, 1)

isTheSame = True

For lngRow = 1 To Range(r.Address).Row + Range(r.Address).Rows.Count - 1
    If r.Cells(lngRow, 1) <> strValue Then
        isTheSame = False
        Exit Function
    End If
End Function

Open in new window

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Martin LissOlder than dirtCommented:
And my apologies if you already know how to add a UDF but just in case...

In Excel, Press Alt+F11 to open Visual Basic Editor (VBE)

Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu

Copy the UDF from my previous post  (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor ("Module1" window)

Press Alt+F11 again to go back to Excel
Robert CarmanAuthor Commented:
Thanks, Martin.  This is normally what I would do if it was something that I ran only on my machine, however, I'm writing reports that other users will run and I would have to create the User Defined Function on each of their machines.  I don't think MS Excel will allow me to automate this by writing code systematically.
Martin LissOlder than dirtCommented:
You can create a template Excel file (xltm) that contains the UDF and then create your Excel workbooks from that template file. Then anyone who needs to do anything with that workbook will have the UDF "built in". I've attached a template file that contains the UDF. EE would not allow it to be uploaded as an xltm file so add the xltm extension. You can of course also change the name.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
barry houdiniCommented:
Hello Robert,

Perhaps use COUNTIF worksheet function with the criteria being the first cell in the range, e.g.


That will return TRUE only if all values in B4:B9 are the same (unless they are all blanks in which case you will get FALSE)

If you need it to be more generic then assuming Rng is a named range of a single column of values (of any length) you can use this version


If you want to allow blanks in the range (so that you would get TRUE when all of the non-blank values in the range are the same) try this version:


regards, barry
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.