asked on

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.

Martin Liss

Please give an example of what you want to be able to do.

Robert Carman

ASKER

Attached is an example of what I would like to achieved with a function or group of functions within Excel.
Difference-Example.jpg

Martin Liss

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
Next
End Function

Open in new window

Martin Liss

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 Carman

ASKER

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.

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.