Comparing Cells-CodePudding

I am trying to get to compare cells between columns and spit out whether the string in range B is found in Range A. If it is found in range A it should return a match ("OK") if values of range B are not found in range A it should list out the values that are not matching.

Something like the below:

Is this possible at all with VBA?

CodePudding user response：

This VBA will do it for you. Create a new module in your workbook and paste this code in ...

Public Function CompareCommaSeparatedValues(ByVal strSearchIn As String, ByVal strSearchFor As String, ByVal strDelimiter As String) As String
    Dim arrSearchFor() As String, i As Long, strSubSearchFor As String
    
    arrSearchFor = Split(strSearchFor, strDelimiter)
    strSearchIn = "," & strSearchIn & ","
    
    For i = 0 To UBound(arrSearchFor)
        strSubSearchFor = "," & arrSearchFor(i) & ","
        
        If InStr(1, strSearchIn, strSubSearchFor, vbTextCompare) = 0 Then
            CompareCommaSeparatedValues = CompareCommaSeparatedValues & "," & arrSearchFor(i)
        End If
    Next
    
    If Left(CompareCommaSeparatedValues, 1) = "," Then CompareCommaSeparatedValues = Mid(CompareCommaSeparatedValues, 2)
End Function

... now just refer to it on sheet.

I've called it CompareCommaSeparatedValues and provided the input for a delimiter but you can rename it as you see fit.

Result

CodePudding user response：

=IF(A2=B2,"OK","Not OK:") https://trumpexcel.com/compare-two-columns/