VBA how to check if three values are the same, excluding cells that are blank?

Question

I have been racking my brain around the best way to check if the cell value in three cells are exactly the same. However, the tricky part is that any one of these three cells can be blank. I have not found a simple solution that does not require a dozen IF and EXACT functions within the cell's formula. I've tried some user defined functions to hopefully reduce the complexity of the cell's formula, but I'm not sure what the best approach is.

I am working on a excel sheet that allows the users to import excel files on to the workbook (see screenshot below). There is code that parses through the imported file's filename and pastes the "E1#" to the appropriate cell (we will call that cell A2. Cell A1 is where the file's directory path is displayed. The "E1#" number for the Quant file goes in A2, the "E1#" number for the Sequence File goes in A4 (file dir goes in A3), and the Order Details "E1#" goes in A6. Each file is optional, so the "E1#"-cells (A2, A4, A6) may or may not have a numeral value in them. The grey cell (A7) should display "Match" because cells A2 and A6 have matching E1# numbers of "1234". If only one cell had a numeral value and the other two E1#-cells were blank, then the grey cell would remain blank. If more than one of the E1# cells are filled with a number and if the numbers do not match each other, then the grey cell displays "Mismatch".

Side note: When I originally created this excel sheet. The users said they only needed Quant and Sequence File fields. So creating a cell formula (within the grey cell) for checking that two cells (A2 and A4) are matching was fairly simple. A couple of IF, ISBLANK and EXACT statements made it work great. But now I had to add the third file field ("Order Details") to the excel sheet. And now I can't figure out how to make the "match/mismatch" checking work now. Is there a better algorithm or code practice that I could be taking? Any advice would be greatly appreciated.

I've tried making a very long cell formula with IF, ISBLANK, and EXACT functions. But the cell formula becomes too complicated to debug and read. I've created a couple user defined functions but I'm still struggling to make a working and cohesive cell formula with it. (Below) my UDF is used in the cell formula with the EXACT function. InterfaceComp_str returns the E1# number or blank.

Public Function InterfaceComp_str(c1 As String, c2 As String) As String
If Len(c1) < 1 Then
    InterfaceComp_str = c2
End If

If Len(c2) < 1 Then
    InterfaceComp_str = c1
End If

If Not IsEmpty(c1) And Not IsEmpty(c2) Then
    If InStr(c1, c2) <> 0 And Len(c1) = Len(c2) Then
        InterfaceComp_str = c2
    End If
End If
End Function`

Here is an example of the cell formula in the grey cell (A7): =IF(EXACT(InterfaceComp_str(A2,A4),InterfaceComp_str(A4,A6)),"Match", "Mismatch")

But there are a lot of issues with this cell formula. I'm not sure if my UDF is the correct approach to tackle this problem.

Answer 1

You can also do this with a worksheet formula.

If there will only be numbers in the cells to check, then:

A7: =LET(
    arr, INDEX(
        A2:A6,
        {1, 3, 5}
    ),
    remBlanks, FILTER(
        arr,
        arr <> ""
    ),
    matchCount, COUNTA(
        FILTER(
            remBlanks,
            INDEX(
                remBlanks,
                1
            ) = remBlanks
        )
    ),
    IF(
        COUNTA(remBlanks) <= 1,
        "",
        IF(
            COUNTA(remBlanks) =
                matchCount,
            "Match",
            "Mismatch"
        )
    )
)

If you need to also test that the values in the cells are numbers, a simple test might include comparing COUNT and COUNTA

A7: =LET(
    arr, INDEX(
        A2:A6,
        {1, 3, 5}
    ),
    remBlanks, FILTER(
        arr,
        (arr <> "")
    ),
    matchCount, COUNTA(
        FILTER(
            remBlanks,
            INDEX(
                remBlanks,
                1
            ) = remBlanks
        )
    ),
    IF(
        COUNTA(remBlanks) <= 1,
        "",
        IF(
            AND(
                COUNTA(
                    remBlanks
                ) = matchCount,
                COUNT(
                    remBlanks
                ) =
                    COUNTA(
                        remBlanks
                    )
            ),
            "Match",
            "Mismatch"
        )
    )
)

Understanding that Excel regards values that look like dates and/or scientific notation numbers as numbers, and what the constraints are on entering values, you might have to check that all of the characters are digits.

Answer 2

Try the function below.

Use like:

=InterfaceComp_str(A2,A3,A4) 'or as many cells as you want to compare

UDF:

Function InterfaceComp_str(ParamArray inputs() As Variant) As String
    Dim i As Long, n As Long, v, firstVal, misMatch As Boolean
    
    For i = 0 To UBound(inputs)       'loop over input cells
        v = CStr(inputs(i))           'current value
        If Len(v) > 0 Then            'non-blank?
            n = n + 1                 'count of non-blank items
            If Len(firstVal) = 0 Then
                firstVal = v          'store first non-blank value
            Else
                If v <> firstVal Then 'different value?
                    misMatch = True   'flag mismatch
                    Exit For          'no need to check further
                End If
            End If
        End If
    Next i
    If n > 1 Then                     '>1 entry?
        InterfaceComp_str = IIf(misMatch, "Mismatch", "Match")
    End If
End Function

Parameter arrays: https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-parameter-arrays

Answer 3

The following VBA code will return true if the inputs are the same, false if the inputs differ (including empty)

'Returns true if all inputs are the same value
Public Function MultiMatch(ParamArray ipArgs() As Variant) As Boolean

    Dim myC As Collection
    Set myC = New Collection
    
    On Error Resume Next
    Dim myItem As Variant
    For Each myItem In ipArgs
        dim myTest as String
        myString = vba.CStr(myItem)
        If VBA.Len(myItem) = 0 Then
            MultiMatch = False
            Exit Function
        End If
        
        ' try to add the key to the collection
        ' an error will be generated if the key already exists
        ' so you might need to format the string if rounding is an issue for single/double values
        myC.Add Key:=myTest, Item:=0
        If Err.Number = 0 Then
            MultiMatch = False
            exit function
        End If
    
    Next
    
    MultiMatch = True
    
End Function